Re: Column to track mu;ltiple foriegn keys

Comments inline.

Brian Tkatch wrote:
>
> I have a table that is a hash of three item ids. The first two ids
> cannot be null and are foreign keyed to specific tables. The third id
> is not always there. If it is, it must reference an id from one of a
> few tables.
> ...
>
> Hash_Table
> ===========
> Item_A_Id (NOT NULL FK)
> Item_B_Id (NOT NULL FK)
> Special _Id (NULL FK)
>
> The third column is tricky. It can point to one of many tables.
>
> I see two possibilities,
>
> 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.

> 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.

>
> Any ideas?

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.

>
> Brian

Martin Received on Sat Jul 21 2001 - 16:27:20 CDT