Le Lundi 2006 Avril 17 03:43, Tom Lane a écrit :
Vincent Frison <firstname.lastname@example.org> 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
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.