Re: Renaming unnamed FK constraints

Le Lundi 2006 Avril 17 03:43, Tom Lane a écrit :
> Vincent Frison <turman@ohmforce.com> writes:
> > I've created my referential integrity with "REFERENCES TABLE..." directly
> > into "CREATE TABLE" statements. I don't have used "ALTER TABLE" with "ADD
> > CONSTRAINT constraint_name..." so I have only unnamed constraint for all
> > my FKs. Maybe I has been to much lazy..
>
> There's no such thing as an unnamed constraint ... PG would have
> generated names for them.  If you're using an old PG version, the
> names might look ugly (like "$1"), but they're there.

Yes this is right on my current PG installation (7.4 on production server, 7.5
on my dev environnement): unnamed constraints are automaticely named with $1,
$2, $3.. But it looks like it was not the case with ealier version isn't it?
I say that because most of my tables was created 2 or 3 years ago (I don't
remember exactly on which PG versions). For this old tables, I type "\d
mytable" with psql there's no foreign-key constraints but only triggers
related to referential integrity. For the newer tables, I can see foreign-key
constraints, altough I created them with the same syntax ("REFERENCES
TABLE..." directly into "CREATE TABLE" statements without naming the
constraint).

> > This is very hapless since unnamed constraints are totally ignored
> > from a lot of SQL clients (PgAdmin3, TOra, SQuirreL...).
>
> Perhaps a name like "$1" would confuse some of those clients, but I
> hardly believe it for PgAdmin.  What problem are you seeing *exactly*?

If I look for constraints for newer tables (i.e. which have FK constraints
named "$1"), I can see them. But for older tables, there's nothing to display
for all theses clients. Of course referential integrity still works but it's
very annoying. For example if I ask to Squirrel or PgAdmin to show SQL for
the table creation, they just omit foreign keys => SQL generation is just
wrong! Another drawback is that I cannot use Squirrel graphs to display my
relation beetween tables since most of foreign key are ignored.