Re: Information Schema and constraint names not unique

Bruce Momjian wrote:

>Tom Lane wrote:
>  
>
>>Alvaro Herrera <alvherre@dcc.uchile.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