Multiple Foreign Keys to same table and field

From: "Robert Fitzpatrick" <robert(at)webtent(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Multiple Foreign Keys to same table and field
Date: 2002-12-23 22:43:32
Message-ID: 000b01c2aad4$bb6d0cf0$bbc2fea9@COLUMBUS
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Is it possible to have multiple Foreign Keys in one table on different
fields related to the same field in one other table?

I create the 'foreignkey1' on 'table1', 'field4' to foreign table
'table2', 'field1' with ON UPDATE and ON DELETE actions set to CASCADE.
No problem, I can update 'field1' in 'table2' and the update cascades
down to 'table1', 'field4'. However, when I add a second 'foreignkey2'
on 'table1', 'field5' to the same foreign table and field 'table2',
'field1', I get an error when updating the 'field1' in 'table2' value
that says 'ERROR: foreignkey1 referential integrity violation - key
referenced from table1 not found in table2'. If I remove the second
foreignkey2, all works well again with foreignkey1.

I'm new to PostgreSQL, or any SQL database for that matter, but I've
worked with relational databases for years where this is not a problem.
I want two fields in table1 to be dependent on one field in table2 and
cascade the updates or deletions as they occur in table2.

--
Robert

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2002-12-23 22:57:11 Re: Multiple Foreign Keys to same table and field
Previous Message Michelle Konzack 2002-12-23 21:32:42 Re: How to backup a postgreSQL of 80 GByte ?