Обсуждение: Renaming unnamed FK constraints
Hello, 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.. This is very hapless since unnamed constraints are totally ignored from a lot of SQL clients (PgAdmin3, TOra, SQuirreL...). The only solution seems to rename all these constraints but I didn't find how to do it. I see somewhere in this ML that I should drop all the triggers related to RI (and then recreate all FKs with "ALTER TABLE" with named constraints). But it looks like a plodding task isn't it? Am I the only one who uses "REFERENCES TABLE..." directly into "CREATE TABLE" statements? Should I go to evil for that? ;) Thanks. Vincent.
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.
> 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*?
regards, tom lane
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.
Vincent Frison <turman@ohmforce.com> writes:
> Yes this is right on my current PG installation (7.4 on production server, 7.5
7.5? There is no PG 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.
Yeah, before about 7.3 there was no pg_constraint catalog and thus no
explicit representation of a foreign key constraint at all. I believe
that if you've just propagated a pre-7.3 schema forward via dump and
reload, you'll still have only the triggers and not any explicit
representation of the foreign key. This has nothing to do with the
syntax you used, though.
My recommendation would be to drop all those old triggers and re-create
the foreign key constraints. You could try contrib/adddepend instead of
doing this by hand.
regards, tom lane
Le Lundi 2006 Avril 17 18:12, Tom Lane a écrit : > Vincent Frison <turman@ohmforce.com> writes: > > Yes this is right on my current PG installation (7.4 on production > > server, 7.5 > > 7.5? There is no PG 7.5. Oops I was confused by the unstable Debian postgresql package whose version is 7.5 (but server parckage is 7.4). > > 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. > > Yeah, before about 7.3 there was no pg_constraint catalog and thus no > explicit representation of a foreign key constraint at all. I believe > that if you've just propagated a pre-7.3 schema forward via dump and > reload, you'll still have only the triggers and not any explicit > representation of the foreign key. This has nothing to do with the > syntax you used, though. Ok thanks a lot for this explanation! > My recommendation would be to drop all those old triggers and re-create > the foreign key constraints. Oh no! :( > You could try contrib/adddepend instead of > doing this by hand. What do you mean by contrib/adddepend? BTW a lot of people should have the same problem (i.e. tables creation with unamed constraints on PG < 7.3) shouldn't they?
Vincent Frison <turman@ohmforce.com> writes: > Le Lundi 2006 Avril 17 18:12, Tom Lane a �crit�: >> You could try contrib/adddepend instead of >> doing this by hand. > What do you mean by contrib/adddepend? There's a Perl script in contrib that will help with this, as well as with some other things that are good to do when updating from pre-7.3 to 7.3. Apparently you didn't see the 7.3 release note suggesting you run it? It's possible that this script is not in the Debian postgresql-contrib package, but if nothing else you could get it from our CVS server: http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/adddepend/ > BTW a lot of people should have the same problem (i.e. tables creation with > unamed constraints on PG < 7.3) shouldn't they? Most people fixed this some time ago ... regards, tom lane
Le Lundi 2006 Avril 17 19:26, Tom Lane a écrit : > Vincent Frison <turman@ohmforce.com> writes: > > Le Lundi 2006 Avril 17 18:12, Tom Lane a écrit : > >> You could try contrib/adddepend instead of > >> doing this by hand. > > > > What do you mean by contrib/adddepend? > > There's a Perl script in contrib that will help with this, as well as > with some other things that are good to do when updating from pre-7.3 > to 7.3. Apparently you didn't see the 7.3 release note suggesting you > run it? Unfortunately I didn't see it, maybe I'm trusting too much Debian scripts (yes it's just slacking). > It's possible that this script is not in the Debian postgresql-contrib > package, This script is not in postgresql-contrib package (it's strange because Debian upgrade script couldn't do a smooth upgrade without it). > but if nothing else you could get it from our CVS server: > http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/adddepend/ Yeah it works perfectly! > > BTW a lot of people should have the same problem (i.e. tables creation > > with unamed constraints on PG < 7.3) shouldn't they? > > Most people fixed this some time ago ... Most people are better admin than I am! (I'm just a developer ;p) Thanks a lot for your support Tom! Regards, Vincent.