Обсуждение: 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
Re: Bug?? -- Alter table drop constraint doesn't seem to work on a primary key constraint in 7.2beta2
От
"Christopher Kings-Lynne"
Дата:
> 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