From: | Kris Jurka <jurka(at)ejurka(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | No dependency between fkey constraint and unique index |
Date: | 2002-09-12 23:20:23 |
Message-ID: | 3D812137.10604@ejurka.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following statements will fail...
CREATE TABLE t1 (a int);
CREATE TABLE t2 (a int references t1(a));
ERROR: UNIQUE constraint matching given keys for referenced table "t1"
not found
But I can do the following...
CREATE TABLE t3 (a int primary key);
CREATE TABLE t4 (a int references t3(a));
ALTER TABLE t3 DROP CONSTRAINT t3_pkey;
There is no dependency generated between the foreign key constraint and
the primary key.
I would like to see a column in pg_constraint confconid that indicated
which unique constraint is supporting the foreign key and the supporting
dependency in pg_depend. This would be useful because you can create
multiple unique constraints over the same set of keys and not know which
one is supporting a foreign key constraint.
CREATE TABLE t5 (a int);
ALTER TABLE t5 ADD CONSTRAINT t5_un_1 UNIQUE (a);
ALTER TABLE t5 ADD CONSTRAINT t5_un_2 UNIQUE (a);
On a somewhat related note...
CREATE UNIQUE INDEX does not add an entry to pg_constraint.
Is this because unique constraints are different from unique indexes in
that the index can be functional and/or partial? Would it be possible
to add an entry to pg_constraint in the simple case?
Kris Jurka
From | Date | Subject | |
---|---|---|---|
Next Message | Rod Taylor | 2002-09-13 14:39:50 | Re: No dependency between fkey constraint and unique index |
Previous Message | Joerg Ludwig | 2002-09-12 22:47:56 | Unable to convert date to tm |