On Wed, Feb 19, 2003 at 13:05:04 -0500,
"Phillip J. Allen" <paallen@attglobal.net> wrote:
> Hi all,
>
> I have two tables 'composition_type' and 'mineral_type'. Both tables
> have a number field for their primary key(comp_id & min_id) and their
> primary keys are unique between the two tables. The third table is
> 'rock_sample' which has a column 'composition'. Now the
> rock_sample.compsition column can be filled with a with either
> compositon_type.comp_id or mineral_type.min_id. So how can I create a
> foreign key for rock_samp.composition referencing
> composition_type.comp_id AND mineral_type.min_id?
This has been covered on one of the lists within the last couple of months.
The idea is to have two foriegn key references to the alternative tables
and use a table constraint to make sure exactly one of them is null.
To go with your current design, you will need to add two columns for
the references and also check that the nonnull value is equal to
rock_sample.composition .