Thread: Foreign Key Unique Constraint can be dropped

On Mon, Jun 8, 2009 at 11:48 AM, Vlado Moravec<vm.devs@gmail.com> wrote:
> Hi,
> from postgres docs: "foreign key must reference columns that either are a
> primary key or form a unique constraint". It does not allow creating foreign
> key constraint on non-unique column - an error is raised.
> After playing with a new database I discovered that such a unique constraint
> can be droped after the foreign key constraint has been established. No
> warning, no error.
> Is such a behaviour intended? I have found no references in the docs.

what version of postgresql you're testing it on ?
you're sure that foreign key isn't dropped as well ?
On 8.3 and 8.4 it doesn't allow me to do it, unless I drop
index/constraint cascade, in which case foreign key constraint gets
dropped as well.

Vlado Moravec <vm.devs@gmail.com> writes:
> from postgres docs: "foreign key must reference columns that either are a
> primary key or form a unique constraint". It does not allow creating foreign
> key constraint on non-unique column - an error is raised.
> After playing with a new database I discovered that such a unique constraint
> can be droped after the foreign key constraint has been established. No
> warning, no error.

Not in any remotely modern version of Postgres.  Maybe you had more than
one unique index, and it chose the other one as the FK's dependency?
There's nothing prohibiting the creation of redundant indexes ...

            regards, tom lane