Thread: No dependency between fkey constraint and unique index

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

> 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

Rod Taylor <rbt@rbt.ca> writes:
> 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.

I have restructured the parsing and execution code for ADD FOREIGN KEY
so that this information is more readily available.  CVS tip will now
create a dependency from the pg_constraint entry for the FK constraint
to the unique index that was found to match the referenced columns.
(Should there be more than one such unique index, I believe you'll get
the one of smallest OID.)

>> On a somewhat related note...
>>
>> CREATE UNIQUE INDEX does not add an entry to pg_constraint.

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

Right, this was a deliberate omission.

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

I don't think so; unique partial indexes, unique functional indexes,
and unique indexes with non-btree access methods are all potentially
useful, but are unsupported by the standard UNIQUE-constraint syntax.
I don't see any value in trying to munge the constraint syntax to
support these things, when the CREATE INDEX command is already there
and perfectly satisfactory.

            regards, tom lane