From: | Gregory Stark <gsstark(at)mit(dot)edu> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | bug with constraint dependencies? or bug with pg_dump/pg_restore? |
Date: | 2003-08-28 15:37:18 |
Message-ID: | 87r835lro1.fsf@stark.dyndns.tv |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2003-08-28 16:02:02 | Re: bug with constraint dependencies? or bug with |
Previous Message | scott.marlowe | 2003-08-28 15:33:15 | Re: Hardware recommendations to scale to silly load |