Hi
I have several columns with an Indes and PK or Fk and do Unload All
several times a month to rebuild the db and I do not think it is causing
any problems.
Is this a big deal?
thanks Marc
>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.
>
> 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
