RE: Constraint names using 'user namespace'?

At 02:18 PM 11/28/00 +0800, Christopher Kings-Lynne wrote:
>> As for the treading-on-user-namespace issue, we already do that for all
>> implicitly created indexes (see UNIQUE, PRIMARY KEY, etc).  I'd prefer
>> to treat named constraints consistently with that long-established
>> practice until we have a better idea that can be implemented uniformly
>> across that whole set of constructs.  (Once we have schemas, for
>> example, it might be practical to give indexes a separate namespace
>> from tables, which'd help a lot.)
>
>Surely the best way to do it would be to make the unique and primary key
>implicitly created indices totally invisible to the user.  Or at least add a
>'system' flag to their entries in the pg_indexes table.  Create a
>pg_constraint table instead that people can use to find constraints.

Oracle has a "user_constraints" table.  Explicitly named contraints have
that name entered into the user's namespace, implicitly named constraints
get stuffed into "sys" in the form "sys.cnnnnn", where "nnnnn" is drawn
from some system sequence.

In Oracle you NEED the user_constraints table, particularly for RI constraint
errors, because their wonderful error messages just give you the RI constraint
name.  If you've not given it a meaningful name yourself, which typically one
doesn't ("integer references some_table"), you need to do a select on the
user_constraints table to see what went wrong.

Keep PG's superior error messages no matter what else is done :)

The above is offered as a datapoint, that's all.

>To support this, dropping unique and pk constraints would no longer be
>possible (and _should_ no longer be possible) with a CREATE/DROP INDEX
>command, and instead would be achieved with a functional ALTER TABLE
>ADD/DROP CONSTRAINT statement.

This is essentially the case in Oracle, though I suspect you could dig
around, find the name of the unannounced unique index, and drop it by
hand if you wanted.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.