It seems when you create a new table with the "references" syntax the constraint is created with a dependency specifically on a "primary key" constraint on the target table.
However when you alter a table to add a foreign key constraint the constraint is added with a dependency on any unique index on the column -- not necessarily a primary key constraint. This causes problems with pg_dump/pg_restore. I'm having trouble restoring my database now for a 7.4 beta test because I get errors like: pg_restore: [archiver (db)] could not execute query: ERROR: there is no UNIQUE constraint matching given keys for referenced table "region" pg_restore: *** aborted because of error In fact there is a unique index, but the indexes aren't created by pg_restore until later in the process. test=> create table test (a integer); CREATE TABLE test=> create unique index test_idx on test (a); CREATE INDEX test=> alter table test add constraint test_pkey primary key (a); ERROR: Existing attribute "a" cannot be a PRIMARY KEY because it is not marked NOT NULL test=> alter table test alter a set not null; ALTER TABLE test=> alter table test add constraint test_pkey primary key (a); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'test_pkey' for table 'test' ALTER TABLE test=> create table test2 (a integer references test); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE TABLE test=> alter table test drop constraint test_pkey; NOTICE: constraint $1 on table test2 depends on index test_pkey ERROR: Cannot drop constraint test_pkey on table test because other objects depend on it Use DROP ... CASCADE to drop the dependent objects too test=> drop table test2; DROP TABLE test=> create table test2 (a integer); CREATE TABLE test=> alter table test2 add constraint test2fk foreign key (a) references foo(a); NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ALTER TABLE test=> alter table test drop constraint test_pkey; ALTER TABLE -- greg ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org