Unable to drop primary key on table with foreign key constraint

[GoogleCodeExporter]

What steps will reproduce the problem?

1. Create two tables, one table has a column that is the primary key.
2. Create a foreign key constraint from first table to the second table, using 
the primary key column.
3. Attempt to drop the primary key.

See attached testcase SQL file for working example.

What is the expected output?

No output is expected; the command should work.

What do you see instead?

Error: org.h2.jdbc.JdbcSQLException: Index "PRIMARY_KEY_41_0" belongs to a 
constraint; SQL statement:
[...]
ALTER TABLE a DROP PRIMARY KEY [90085-170]

What version of the product are you using?

v1.3.170

On what operating system, file
system, and virtual machine?

On Linux (Debian unstable) 3.2.0-4-686-pae.

Do you know a workaround?

Yes.

The work-around is to drop the foreign-key constraint before dropping the 
primary key then recreate the foreign-key constraint.

The testcase includes this work-around as two commented-out SQL commands

What is your use case, meaning why do you need this feature?

I want to modify a primary key for a table.  It was originally created using a 
single column and I wish to modify this so it uses two columns.  I'm using 
liquibase to manage such schema migration and H2 for unit-testing.

How important/urgent is the problem for you?

I have a work-around and H2 is used for unit-testing only, so the problem is 
less urgent for me.

Please provide any additional information below.

The problem seems superficially similar to another problem, reported as issue 
238:

    http://code.google.com/p/h2database/issues/detail?id=238

Although this issue is marked 'fixed' it may be that issue 238 and this issue 
suffer from a common problem that remains unresolved.

Original issue reported on code.google.com by A.P.Mil...@gmail.com on 26 Feb 2013 at 7:18

Attachments:

• testcase-drop-primarykey.sql

[GoogleCodeExporter]

This is not a bug. If you create an explicit foreign key, you are expected to 
explicitly drop it.

Original comment by noelgrandin on 4 Mar 2013 at 12:15

• Changed state: Invalid

[GoogleCodeExporter]

Sorry, could you explain this further:  why must I explicitly drop a foreign 
key constraint to drop a primary key constraint?

Other databases do not have this requirement.

Original comment by A.P.Mil...@gmail.com on 4 Mar 2013 at 5:39

[GoogleCodeExporter]

Marking this as NEW so Thomas can check it out.

Original comment by noelgrandin on 2 Apr 2013 at 8:24

• Changed state: New

[GoogleCodeExporter]

We use indexes internally to enforce foreign key constraints.

If you had not created a primary key constraint, we would have automatically 
created a unique index in order to make the foreign key constraint work.

But since you did, we re-used that primary key index for the foreign key 
constraint.

Which means that dropping the primary key index is not allowed.

Sorry, but I can't see any way of making this work any better than it does now.

Original comment by noelgrandin on 18 Jul 2013 at 12:53

• Changed state: WontFix