Thread: Bug?? -- Alter table drop constraint doesn't seem to work on a primary key constraint in 7.2beta2

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.

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,

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

Barry Lind <barry@xythos.com> 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

Barry Lind <barry@xythos.com> 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

> Barry Lind <barry@xythos.com> 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

"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