FAQ
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.

Search Discussions

  • Tom Lane at Apr 17, 2006 at 1:43 am

    Vincent Frison 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
  • Vincent Frison at Apr 17, 2006 at 3:26 pm

    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.
  • Tom Lane at Apr 17, 2006 at 4:13 pm

    Vincent Frison 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
  • Vincent Frison at Apr 17, 2006 at 4:38 pm

    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?
  • Tom Lane at Apr 17, 2006 at 5:26 pm

    Vincent Frison 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
  • Vincent Frison at Apr 17, 2006 at 6:17 pm

    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 17, '06 at 12:40a
activeApr 17, '06 at 6:17p
posts7
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Vincent Frison: 4 posts Tom Lane: 3 posts

People

Translate

site design / logo © 2022 Grokbase