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.
