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