Cannot drop primary key

[Thomas Kellerer]

Hi,

maybe I'm missing something, but the following statement

ALTER TABLE my_table DROP PRIMARY KEY;

fails with the error message:

Index "PRIMARY_KEY_14E" belongs to a constraint

When I run

ALTER TABLE my_table ADD CONSTRAINT PK_MY_TABLE

it is working.

According to the SQL Grammer description I would expect the first
statement to work just like the second.

Is this a bug in the code or in the documentation, or am I wrong
assuming that the first statement should work?

Regards
Thomas

[Thomas Kellerer]

OK I found the reason for this.

The PK columns are part of a foreign key to another table.

So I need to drop the FK constraints first

My use case is this: I want to add a new column to the primary key of
that table. With Oracle I can simply drop the primary key, add the new
column and add the new PK.

With H2 I need to

drop the foreign key of the first PK column
drop the foreign key of the second PK column
drop the primary key
add the column
add the new primary key
re-add the foreign key for the first pk column
re-add the foreign key for the second pk column

There is a similar "issue" when dropping a column that is a (single
column) foreign key. It cannot be dropped unless I drop the FK
constraint first :(

On Feb 5, 10:31 am, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Thomas Mueller]

Hi,

I don't think I will change the behavior currently. My test case is:

drop table test;
create table test(id int constraint x primary key, parent int);
alter table test drop constraint x;
drop table test;
create table test(id int constraint x primary key, parent int
references test(id));
alter table test drop constraint x;

PostgreSQL throws the following exception:
Cannot drop constraint x on table test because other objects depend on it
HSQLDB: dependent objects exist

> Is this a bug in the code or in the documentation

The exception message is clear I believe.

Regards,
Thomas