I need some help understanding and creating foreign keys in postgresql.
Here is an example of what I am trying to do:
I have table 1 with:
table1_id serial unique
table1_col1 varchar
I have table2 with:
table2_id serial unique
table1_id integer
table2_col1 varchar
When I try to create the foreign key with
alter table "schema_name"."table1"
add foreign key ("table1_id")
references "schema_name"."table2"("table1_id")
on delete cascade
on update cascade
not deferrable;
Postgres complains with:
ERROR: UNIQUE constraint matching given keys for referenced table "table2" not
found.
Why is postgresql demanding a unique key on table2.table1_id? It is a foreign
key in a parent/child 1 to many relationship.
Please help me understand what is going on, and what I am missunderstanding
about foreign keys.
Thanks
chris
Postgresql 7.3.4 on RH ES 2.1
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
