Thread: Foriegn key to Union query or two tables

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?

I know some might/will say why are the two tables not just merged into
one but they are under normal circumstances two different things with
difference mutually exclusive attributes.  Also in other situations I
need them appart.

Any suggestions?  I just tried adding a foreign key referencing a VIEW
with a UNION query and psql just spit it back.

Thanks,

Phillip J. Allen
Consulting Geochemist/Geologist
Lima Peru
e-mail: paallen@attglobal.net

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 .