Re: No dependency between fkey constraint and unique index

> 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);

Technically, either of these should be able to support the foreign key.
Perhaps we should throw an error when the user tries to create a
duplicate unique constraint, stating that they already have one and the
second will simply decrease performance.

I do agree that a dependency entry from the foreign key constraint to
the unique or primary key constraint would be useful. That information
isn't immediately available, as the initial check is performed early on
in the parser. The constraint creation code makes the assumption that
one already exists.

> 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?

The reason that this wasn't done, is that it changes the properties of
old style unique constraints. You'll notice we also left old style
foreign keys, and serial columns alone as well.

If you wish to convert and old style method to a new style one, you can
use the upgrade.pl tool available at
http://www.rbt.ca/postgresql/upgrade.shtml

I'm hoping to depreciate this method of creating a unique index, but it
will take a number of releases before it can be done.

--
Rod Taylor