Thread: Uniqueness of rule, constraint, and trigger names

Currently we have a rather confusing mismash of behaviors for the names
of rules, constraints, and triggers.  I'd like to unify the rules
so that these objects all have the same naming behavior; and the only
behavior that makes sense to me now is that of triggers.

The current behavior is:

1. Rules are required to have a name that is unique within the current
database.  The rule can be named without reference to the table it is
on.  Dropping a rule is done with "DROP RULE name".

2. Constraints are not required to have any unique name at all.
Dropping constraints is done with "ALTER TABLE tablename DROP CONSTRAINT
constraintname", which will drop all constraints on that table that
match the given name.

3. Triggers are required to have names that are unique among the
triggers on a given table.  Dropping a trigger is done with "DROP
TRIGGER name ON table".

The SQL spec is not a great deal of help on this, since it doesn't
have rules or triggers at all.  For constraints, it requires
database-wide uniqueness of constraint names --- a rule I doubt
anyone is going to favor adopting for Postgres.

I think that all three object types should have names that are unique
among the objects associated with a particular table, but not unique
across a whole database.  So, triggers are okay already, but rules
and constraints need work.

For rules, we'd need to change the syntax of DROP RULE to be "DROP RULE
name ON table", much like DROP TRIGGER.  This seems unlikely to cause
problems for existing applications, since I doubt rule-dropping is done
much by application code.

For constraints, we'd need to change the code to be more careful to
generate unique names for unnamed constraints.  That doesn't seem
difficult, but I'm a little worried about the possibility of errors
in loading schemas from existing databases, where there might be
non-unique constraint names.  Perhaps it'd be safer to maintain the
current behavior (no uniqueness required for constraint names).

Comments?
        regards, tom lane

On Mon, 4 Mar 2002, Tom Lane wrote:

> For constraints, we'd need to change the code to be more careful to
> generate unique names for unnamed constraints.  That doesn't seem

Another question would be what to do with inherited constraints that
conflict in multiple inheritance cases.  It'd probably be safe to rename
those on the child table to be unique, but then drop constraint may
become more involved (and the error messages no longer use the name
given by the user for either constraint).

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Mon, 4 Mar 2002, Tom Lane wrote:
>> For constraints, we'd need to change the code to be more careful to
>> generate unique names for unnamed constraints.  That doesn't seem

> Another question would be what to do with inherited constraints that
> conflict in multiple inheritance cases.  It'd probably be safe to rename
> those on the child table to be unique,

I'd just raise an error, I think, unless perhaps the constraints are
identical (for some definition of identical).  We don't allow
conflicting column definitions to be inherited, so why constraints?
        regards, tom lane

On Mon, 4 Mar 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > On Mon, 4 Mar 2002, Tom Lane wrote:
> >> For constraints, we'd need to change the code to be more careful to
> >> generate unique names for unnamed constraints.  That doesn't seem
>
> > Another question would be what to do with inherited constraints that
> > conflict in multiple inheritance cases.  It'd probably be safe to rename
> > those on the child table to be unique,
>
> I'd just raise an error, I think, unless perhaps the constraints are
> identical (for some definition of identical).  We don't allow
> conflicting column definitions to be inherited, so why constraints?

Good point.  That's probably better than autorenaming them.

> 2. Constraints are not required to have any unique name at all.
> Dropping constraints is done with "ALTER TABLE tablename DROP CONSTRAINT
> constraintname", which will drop all constraints on that table that
> match the given name.

Personally, I'd like to see CHECK contraints given decent names (eg.
mytable_fullname_chk) instead of '$1', '$2', etc.  This makes it much easier
to use DROP CONSTRAINT...

Also, when it comes time to let people drop FOREIGN KEY constraints, it
might be a problem that they're all generated as '<unnamed>' at the
moment...

Chris

On 4 Mar 2002 at 14:24, Tom Lane wrote:

> For constraints, we'd need to change the code to be more careful to
> generate unique names for unnamed constraints.  That doesn't seem
> difficult, but I'm a little worried about the possibility of errors
> in loading schemas from existing databases, where there might be
> non-unique constraint names.

Create a tool to generate unique constraint names during a dump.  Sounds 
like a pg_dump[all] switch to me.

> Perhaps it'd be safer to maintain the
> current behavior (no uniqueness required for constraint names).

I would rather see a simple and unambigous way to maintain a single 
constraint.  Perhaps a "unique/not-unique" knob is appropriate.
-- 
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples

Tom,

> Currently we have a rather confusing mismash of behaviors for the
>  names
> of rules, constraints, and triggers.  I'd like to unify the rules
> so that these objects all have the same naming behavior; and the only
> behavior that makes sense to me now is that of triggers.

I agree.  

Regarding prioritization:  As a heavy user of constraints and triggers,on two commercial projects, I have yet to have
constraintnamesoverlap.  What's more of a problem for me is those pesky <unnamed>constraints.
 


-Josh Berkus

Tom Lane writes:

> The SQL spec is not a great deal of help on this, since it doesn't
> have rules or triggers at all.

The SQL spec has triggers, and their names are supposed to be
globally unique.

> For constraints, it requires database-wide uniqueness of constraint
> names --- a rule I doubt anyone is going to favor adopting for
> Postgres.

This should probably be schema-wide, which poses much less of a problem.

-- 
Peter Eisentraut   peter_e@gmx.net

> > The SQL spec is not a great deal of help on this, since it doesn't
> > have rules or triggers at all.
> The SQL spec has triggers, and their names are supposed to be
> globally unique.

Speaking of which, I was looking at
 CREATE ASSERTION/DROP ASSERTION

in (what I think is) SQL99. It has enough restrictions on use that I'm
not yet sure what exactly it is supposed to do, and am not sure if it is
close to equivalent to something we already have. Anyone know?
                  - Thomas