Re: Data and Referential Integrity

On 12 Feb 2002 23:19:33 GMT, ajsteiner_at_aol.comnospam (Adam Steiner) wrote:
>Basically, if
>the 3rd table has a 1:M relationship with both tblClosed and tblMain, will it
>check both of them each time a deletion is made? Is there a smarter way to do
>this?

Can't quite follow your question.

Presume you're putting open tickets, whatever, into tblMain. When they're closed, you'll delete them from tblMain and insert them into tblClosed. Now, that's fine, but there's no referential integrity until there's a third table.

If the third table is supposed to be 1:m detail lines for a ticket, then you can't very well put DRI to both tblMain and tblClosed because, yes, it would complain. What you'd want to do is to put in a new table, tblSuper, that has nothing in it but the PK, and have 1:1 relations to tblMain and tblClosed, and 1:m to tbl3.

I don't know of a name for that pattern, tho I've done it a few times.

Joshua Stern
JRStern_at_gte.net Received on Wed Feb 13 2002 - 02:24:23 CET