Re: Multiple Foreign Keys to same table and field

> "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