June 16, 2008 at 10:10 am
How do you remove a row with a 'catch 22' scenerio
eg. Table1(PK_iTable1ID, FK_iTable2ID)
Table2(PK_iTable2ID, FK_iTable1ID)
and for that matter, how do you add entries?
June 16, 2008 at 10:19 am
This doesnt make sense
Are you saying that table 1 has a pk, with a FK to table 2 (PK), and table 2 has a pk, with a FK to table 1 (PK).
Where would you want to use something like this?
~PD
June 16, 2008 at 10:25 am
We have a depricated product that had entries on it's table. The customer had users tied to the table. When reavtivating the customer (they were using our product 5 years ago), we had a bunch of old users to clear out.
we have a user table with it's PK being referenced from 2 different (depricated tables). These 2 tables were stuck in a catch 22 with eachother, and because I couldn't delete their entries, I couldn't remove the old user.
The fast solution was going in an removing the FK contraint, removing the old users, then adding the constraint back.
June 16, 2008 at 10:37 am
That's what I suggest. Remove the constraints (or disable), delete, turn back on.
June 16, 2008 at 11:27 am
You could also set one of the records to point to another record or NULL if it is allowed.
Typically, in these circular reference scenarios, one of the records would be inserted with a NULL in the FK field, the second record would be inserted, and then the first record would be updated. You can do the opposite to remove them.
June 16, 2008 at 11:28 am
These types of references are the typical example of why you need deferred updates as they have in Oracle.
In my opinion, 99% these types of "Chicken or Egg" references are just bad database design.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply