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

Search Discussions

  • Bruno Wolff III at Feb 19, 2003 at 6:31 pm

    On Wed, Feb 19, 2003 at 13:05:04 -0500, "Phillip J. Allen" 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 .

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 19, '03 at 6:06p
activeFeb 19, '03 at 6:31p
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase