On Tue, 15 May 2001 16:13:30 -0400, ForenSys wrote:
>It's the same in 6.1.
>
>Is this the proper behaviour?
Stephen,
Perhaps it would be nice if a retroactive attempt to build an FK on non-
conforming data gave a warning and aborted. To me, proper behaviour
begins at database design, and retroactive creation of constraints
implies that there have been problems in the data integrity that need to
be corrected.
Microrim, RBTI, and other R:Base consulting and training groups have
been teaching since R:Base 3.0 that you should inspect all data before
retroactively adding constraints to already-populated tables.
For example, here's what David Blocker and/or I wrote in 1995 (ca.
version 4.5, I think) or earlier, in the seminar/book "Advanced R:BASE
-- Programming" (greatly condensed from the original, which has
sample commands and other details):
"To add constraints to an existing table, you should follow these steps:
1) Drop any RULE that formerly enforced uniqueness for this table.
2) Drop any former validation rules for the table.
3) Drop any delete restriction rules protecting the table.
4) For the column that will be the unique column, make sure there are
no NULL values.
5) For each column that will be a foreign key, make sure the primary
key table already exists, and already has its primary key successfully
defined accodring to these steps, and that all the values you may need
are already in that table.
6) Be absolutely sure there are no duplicate values in your candidate
primary key columns. Even if you used to have a rule, you could still
ahve a duplicate value, if the rule was created after rows were already
in the database, or if rules are ever set OFF while data is loaded.
7) Drop any former index on the unique column or future foreign key
column
8) Create the primary key and foreign key constraints.
Bill
