David,
Long time no hear from you. I think your probem results from
having an INDEX and a PRIMARY KEY on the same column. From what
I remember. when Constraints (PRIMARY KEY, UNIQUE INDEX, FOREIGN
KEY) were first introduced you could have both. Later this was
tightened up and both a constraint and index were not allowed.
Since the constraints are implemented via Indexes a regular
Index is redundant. Probably document for constraints should
contain a warning that if you have an Index on the column(s) you
wish to add a PRIMARY/UNIQUE constraint you need to first Remove
the index.
To Help you analyze your database check out the additions to the
LIST command such as PKEY or UKEY or FKEY.
Jim Bentley
--- "David M. Blocker" <[EMAIL PROTECTED]> wrote:
> Hi Friends
>
>
>
> I recently installed an upgrade for a client from R:Base for
> DOS to R:Base
> 7.1 (yes, I know - this was long delayed and they were
> unwilling to go for
> 7.6 yet. That will be next.).
>
>
>
> As part of the upgrade, I added several PK / FK relationships
> where in the
> old version they had used just indexes. When they ran my
> automated check
> the database / pack keys / backup routine, the new constraints
> "broke". The
> data itself was fine, but if you did a:
>
>
>
> SELECT * from table2 WHERE FKcolumn NOT IN (SELECT PKColumn
> from Table1),
>
>
>
> ALL the rows in table 2 came back.
>
>
>
> I believe I've fixed this by doing an UNLOAD ALL to file, and
> INPUT to
> recreate the database.
>
>
>
> But the real issue is how they discovered the problem.
>
>
>
> When the program hit the PACK KEYS command, R:Base found the
> broken index
> AND STOPPED the pack keys process. They got the constraint
> error message
> about invalid FK on the screen, even though ERROR MESSAGES and
> MESSAGES were
> both set OFF. But what they did NOT get was my PROGRAMMED
> message that the
> PACK KEYS had failed and to contact me. The program instead
> merrily went on
> its way assuming the database was OK and backed it up.
>
>
>
> Here's the code that was failing:
>
>
>
> SET ERR VAR gError
>
> PACK KEYS
>
> SET VAR vErrHold = .gError
>
> IF vErrHold <> 0 THEN
>
> -- code for error message
>
> GOTO BOTTOM
>
> ENDIF
>
>
>
> The error message the DID get - the constraint violation
> message - of course
> made little sense the user in the context of the backup
> process, but
> fortunately he was sharp enough to know something was up and
> to call me.
>
>
>
> When the indexes were broken, and a PACK KEYS failed, the
> error variable at
> the R> prompt was NOT 0, but when I traced the program, it was
> right after
> the PACK KEYS
>
>
>
> So 2 questions:
>
>
>
> 1. Is this is a known problem in 7.1 and has it been
> addressed in later
> versions?
>
> 2. If this is NOT a bug, and R:Base is behaving
> correctly, is there
> another way to trap for the issue in a program and give the
> user a
> customized error message?
>
>
>
> David Blocker
>
> David M. Blocker
> [EMAIL PROTECTED]
> 781-344-1920
> Cell Phone: 339-206-0261
>
>
>
Jim Bentley
American Celiac Society
[EMAIL PROTECTED]
tel: 1-504-737-3293
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
