I am trying to use the new 'alter table drop constraint' syntax to drop
a primary key constraint (or foreign key constraint) on the 7.2beta2
code. It gives me the following error:

files31=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2b2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

files31=# alter table XYF_FILES DROP CONSTRAINT XYF_FILES_PK;
ERROR: parser: parse error at or near ";"


When I look at the definition on the table through psql \d I can see
that this constraint does indeed exist. What am I doing wrong?

I am assuming that this syntax should work because it is documented in
the 7.2 docs.

thanks,
--Barry

PS I also notice that 'alter table drop constraint' doesn't appear to be
tested at all in the alter_table regression test. 'alter table add
constraint' is tested however.

Search Discussions

  • Tom Lane at Nov 10, 2001 at 12:16 am

    Barry Lind writes:
    files31=# alter table XYF_FILES DROP CONSTRAINT XYF_FILES_PK;
    ERROR: parser: parse error at or near ";"
    You forgot the RESTRICT/CASCADE option.
    I am assuming that this syntax should work because it is documented in
    the 7.2 docs.
    If it's documented without the option then the docs are in error;
    where are you looking?

    regards, tom lane
  • Barry Lind at Nov 10, 2001 at 12:41 am
    Tom,

    I was looking at the 7.2 docs online at developer.postgresql.org. The
    only example of 'drop constraint' in the text for the 'alter table'
    command shows its usage without the RESTRICT/CASCADE option. I also
    noticed that RESTRICT/CASCADE is not defined in the description of alter
    table so I am not really sure what each does.

    But I still can't get it to work for me. Consider the following test case:

    create table test (col_a integer not null, col_b text);
    alter table test add constraint test_pk primary key (col_a);
    alter table test drop constraint test_pk restrict;
    alter table test drop constraint test_pk cascade;

    produces the following output:

    files31=# \i test.sql
    CREATE
    psql:test.sql:6: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create
    implicit index 'test_pk' for table 'test'
    CREATE
    psql:test.sql:8: ERROR: ALTER TABLE / DROP CONSTRAINT: test_pk does not
    exist
    psql:test.sql:10: ERROR: ALTER TABLE / DROP CONSTRAINT does not support
    the CASCADE keyword


    Notice that it doesn't seem to be able to drop the primary key
    constraint that was just created when I use the RESTRICT keyword and it
    claims not to support the CASCADE keyword at all.

    thanks,
    --Barry



    Tom Lane wrote:
    Barry Lind <barry@xythos.com> writes:
    files31=# alter table XYF_FILES DROP CONSTRAINT XYF_FILES_PK;
    ERROR: parser: parse error at or near ";"
    You forgot the RESTRICT/CASCADE option.

    I am assuming that this syntax should work because it is documented in
    the 7.2 docs.
    If it's documented without the option then the docs are in error;
    where are you looking?

    regards, tom lane
  • Tom Lane at Nov 10, 2001 at 12:49 am

    Barry Lind writes:
    I was looking at the 7.2 docs online at developer.postgresql.org. The
    only example of 'drop constraint' in the text for the 'alter table'
    command shows its usage without the RESTRICT/CASCADE option. I also
    noticed that RESTRICT/CASCADE is not defined in the description of alter
    table so I am not really sure what each does.
    But I still can't get it to work for me.
    Okay, looks like we have both some doco and some code issues to fix ...
    thanks for the report.
    Notice that it doesn't seem to be able to drop the primary key
    constraint that was just created when I use the RESTRICT keyword and it
    claims not to support the CASCADE keyword at all.
    I can believe that CASCADE might be a not-yet-supported option, but
    the simpler case ought to work.

    regards, tom lane
  • Tom Lane at Nov 10, 2001 at 8:17 pm

    Barry Lind writes:
    I was looking at the 7.2 docs online at developer.postgresql.org. The
    only example of 'drop constraint' in the text for the 'alter table'
    command shows its usage without the RESTRICT/CASCADE option.
    Erroneous example fixed.
    But I still can't get it to work for me. Consider the following test case:
    create table test (col_a integer not null, col_b text);
    alter table test add constraint test_pk primary key (col_a);
    alter table test drop constraint test_pk restrict;
    psql:test.sql:8: ERROR: ALTER TABLE / DROP CONSTRAINT: test_pk does not
    exist
    Looking at the code, the problem is that DROP CONSTRAINT only works with
    CHECK constraints at the moment. This does seem to be adequately
    documented. Improving the functionality will have to wait for some
    future development cycle.

    regards, tom lane
  • Christopher Kings-Lynne at Nov 12, 2001 at 2:37 am

    Barry Lind writes:
    I was looking at the 7.2 docs online at developer.postgresql.org. The
    only example of 'drop constraint' in the text for the 'alter table'
    command shows its usage without the RESTRICT/CASCADE option.
    I could have sworn that the documentation patch for this specified that the
    restrict clause was needed.

    Here you go (from the Notes section):

    In DROP CONSTRAINT, the RESTRICT keyword is required, although dependencies
    are not yet checked. The CASCADE option is unsupported. Currently DROP
    CONSTRAINT drops only CHECK constraints. To remove a PRIMARY or UNIQUE
    constraint, drop the relevant index using the DROP INDEX command. To remove
    FOREIGN KEY constraints you need to recreate and reload the table, using
    other parameters to the CREATE TABLE command.
    Erroneous example fixed.
    But I still can't get it to work for me. Consider the
    following test case:
    create table test (col_a integer not null, col_b text);
    alter table test add constraint test_pk primary key (col_a);
    alter table test drop constraint test_pk restrict;
    psql:test.sql:8: ERROR: ALTER TABLE / DROP CONSTRAINT: test_pk does not
    exist
    Looking at the code, the problem is that DROP CONSTRAINT only works with
    CHECK constraints at the moment. This does seem to be adequately
    documented. Improving the functionality will have to wait for some
    future development cycle.
    DROP CONSTRAINT can only drop CHECK constraints at the moment - and I could
    also have sworn that that was documented in my SGML patch!

    Perhaps the fall-through message about the constraint not existing is
    somewhat misleading...

    Anyway Barry - you can just go "DROP INDEX test_pk". In fact -

    Chris
  • Tom Lane at Nov 12, 2001 at 2:41 am

    "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
    I could have sworn that the documentation patch for this specified that the
    restrict clause was needed.
    So it did, but there was an example further down with no RESTRICT.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedNov 9, '01 at 11:11p
activeNov 12, '01 at 2:41a
posts7
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase