Re: Column to track mu;ltiple foriegn keys

>> 1) Have a foreign key for each possible Special.
>
>This will not work if they do not contain exactly the same IDs because then you
>will always have the case where the special_id is contained in one of the
>special tables but not in another.

I meant that there would be one column per special table. No NOT NULLs, but each could be individually Foreign Keyed. A constraint could just make sure that no more than one is not null at any given time.

>> 2) Have one column for each special, and another column to specify
>> which special is being referenced.
>
>This is one possibility, but may urge you to add more columns and to keep track
>of their contents.

That's what the column identifiewr would be for.

>Yes. Combine all your special tables into one and distinguish the assignment to
>a special table by one distinguisher column. Then you can easily establish and
>maintain referential constraints.

That is an interesting idea. But then I could not force any NOT NULL constraints on any of the columns (in the new special table (made from all the smaller tables.) that are not common to *all* the specials.

Hmm.., unless you mean that all the tables have one sequence. And the sequence would be popped into *both* the specific special table *and* a new id table, that just has the ids to force the foreign key. I'd then need a foreign key on each special tsable, and a trigger on the parent id table.

Brian Received on Sat Jul 21 2001 - 16:27:41 CDT