Thread: How to rename a constraint/trigger??

How to rename a constraint/trigger??

From
"Andrei Bintintan"
Date:
Hi to all,
 
I have a database, where in the tables I have around 100 constrains (link to other tables) that don't have a name "<unnamed>" or they have a name like "$1" "$2". Now, I have a module which bases on the same structure, but I get some query errors from a "<unnamed>" constraint. I really don't know exaclty  which the constrain generates the error, in that table I have 12 such constrains.
 
Does anyone have a script or it exist a command so that I can rename all the "<unnamed>"constrains in this database?
 
I'm using postgre 7.3.2.
 
Thankx,
Andy.
 
 

Re: [SQL] How to rename a constraint/trigger??

From
Josh Berkus
Date:
Andrei,

> I have a database, where in the tables I have around 100 constrains (link
> to other tables) that don't have a name "<unnamed>" or they have a name
> like "$1" "$2". Now, I have a module which bases on the same structure, but
> I get some query errors from a "<unnamed>" constraint. I really don't know
> exaclty  which the constrain generates the error, in that table I have 12
> such constrains.

You'll be happy to know that in 7.4 we stopped using "<unnamed>".

> Does anyone have a script or it exist a command so that I can rename all
> the "<unnamed>"constrains in this database?

You can't rename constraints, you have to drop and re-create them under a new
name (do this in a transaction for data safety).  However, this requires
knowing what all those constraints do.   I'd suggest doing a text pg_dump
file of your schema only (no data) and looking in the constraints section at
the end.

--
Josh Berkus
Aglio Database Solutions
San Francisco