Re: Information Schema and constraint names not unique

Bruce Momjian wrote:

>Tom Lane wrote:
>
>
>>Alvaro Herrera <alvherre(at)dcc(dot)uchile(dot)cl> writes:
>>
>>
>>>On Thu, Nov 06, 2003 at 11:42:13AM -0500, Tom Lane wrote:
>>>
>>>
>>>>I don't have a problem with switching from "$1" to "tablename_$1", or
>>>>some such, for auto-generated constraint names. But if it's not
>>>>guaranteed unique, does it really satisfy Philip's concern?
>>>>
>>>>
>>>It certainly _is_ unique within a schema ...
>>>(But what happens to the constraint name when the table is renamed?)
>>>
>>>
>>Exactly. Also consider manually-assigned constraint names that happen
>>to look like "foo_$n" --- these could cause trouble if table foo is
>>created later. To make a guarantee of uniqueness would require more
>>infrastructure than just a simple hack of the constraint name generator
>>logic.
>>
>>BTW we also have some problems with auto-generated names for column
>>constraints; these generally look like "tablename_columnname", and
>>that's not unique:
>>
>>regression=# create table foo (f1 int check (f1 > 0) check (f1 < 10));
>>ERROR: check constraint "foo_f1" already exists
>>
>>
>
>Is this a TODO to fix?
>
>

I think there are several of them from this thread:

. make autogenerated column constraint names unique per table (by adding
"_$n" ?)
. add tableoid or tablename to information_schema.{check_constraints,
referential_constraints} (I think those are the only places where it
would be needed, from my quick skimming).
. add tableoid or tablename to autogenerated table constraint names

Is that a fair summary of discussion so far?

(My take)
Using tableoid instead of tablename avoids renaming problems, but makes
the names horribly opaque IMNSHO. I know I've been annoyed from an
aesthetic POV more than once by the "$1" thing.

cheers

andrew