Dear Tom,
The issue is that when one does (in pg 8.3.5)
ALTER TABLE foo ADD CONSTRAINT xxx UNIQUE ON (...);
this results in a constraint *and* an index, but when one does only the
corresponding:
CREATE UNIQUE INDEX foo(...);
then the index is created but there is no constraint.
This is intentional. You didn't create a constraint in the sense of the
SQL standard, and furthermore it may very well be impossible to
represent the index as a constraint in information_schema. (For
instance, the index might be functional or partial --- in fact, it most
likely is special in some way, or you'd not have bothered to use the
nonstandard syntax to make it.)
Ok. I can understand that.
ISTM that I still have a bug: I have a query on the information_schema
which returns stupid results because there is no matching constraint.
The other way to "fix" is that the "foreign key" declaration should be
rejected because there is no unique constraint on the target attribute. I
guess that the FK checks that there is an index while it should
(logically) check that there is a unique constraint, which implies the
index.
Thanks for your answer,
--
Fabien.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers