Обсуждение: Multiple Foreign Keys to same table and field

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

"Robert Fitzpatrick" <robert@webtent.com> writes:
> Is it possible to have multiple Foreign Keys in one table on different
> fields related to the same field in one other table?

Offhand I think that should work.  Could we see a complete example of
what's going wrong for you?

            regards, tom lane

> "Robert Fitzpatrick" <robert@webtent.com> writes:
> > Is it possible to have multiple Foreign Keys in one table
> on different
> > fields related to the same field in one other table?
>
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Offhand I think that should work.  Could we see a complete
> example of what's going wrong for you?
>
>             regards, tom lane
>

Thanks for the quick response. Here is what I did to create all the
tables:

CREATE TABLE "table1" (
"field1" varchar(10) NOT NULL PRIMARY KEY, "field2" char(12), "field3"
char(12)
)

CREATE TABLE "table2" (
field1 char(12) NOT NULL PRIMARY KEY, field2 char(12)
)

ALTER TABLE "table1" ADD CONSTRAINT "field2_table2_field1" FOREIGN KEY
("field2") REFERENCES "table2"("field1") ON DELETE CASCADE ON UPDATE
CASCADE NOT DEFERRABLE;

ALTER TABLE "table1" ADD CONSTRAINT "field3_table2_field1" FOREIGN KEY
("field3") REFERENCES "table2"("field1") ON DELETE CASCADE ON UPDATE
CASCADE NOT DEFERRABLE;

INSERT INTO "table2" ("field1", "field2") VALUES ('test', 'test')

INSERT INTO "table1" ("field1", "field2", "field3") VALUES ('test',
'test', 'test')

Now, I do the update and the resulting error message:

UPDATE "table2" SET "field1" = 'test1', "field2" = 'test ' WHERE
"field1" = 'test '

ERROR: field2_table2_field1 referential integrity violation - key
referenced from table1 not found in table2

--
Robert

>
> It seems to work correctly in 7.3.  I see a couple different
> CVS log entries that might be the relevant fix --- try
> Stephan Szabo if you want details.
>

Thanks for the tip, I upgraded to 7.3 and all works fine.

--
Robert

On Mon, 23 Dec 2002, Robert Fitzpatrick wrote:

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

It should be in some cases.  What version are you using?  If it's not 7.3
then you'll definately have problems I think since I believe one set of
the fixes may have gone in between 7.2 and 7.3, but if you're using 7.3
then I may have missed a case.