No dependency between fkey constraint and unique index

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

Responses

Browse pgsql-bugs by date

  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