Drop constraint does not drop its indices

[GoogleCodeExporter]

H2 shows a different behavior if the foreign key constraints are created after 
unique constraints. There is no index for the foreign key constraint and when 
dropping unique constraint it does not drop its indices.

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)

-- execute this step-by-step

CREATE TABLE parent (
    id bigint PRIMARY KEY
);

CREATE TABLE child (
    id bigint PRIMARY KEY,
    fk bigint,
    name varchar(255),
    CONSTRAINT fk_child FOREIGN KEY (fk) REFERENCES parent (id),
    CONSTRAINT key_child UNIQUE (fk, name)
);

-- the foreign key constraint was created before the unique constraint

SELECT index_type_name, index_name, column_name, sql 
FROM information_schema.indexes
WHERE table_name='CHILD';

-- there are 4 indices: one for the PK, one for the FK and two for the unique 
constraint

ALTER TABLE child DROP CONSTRAINT key_child;

SELECT index_type_name, index_name, column_name, sql 
FROM information_schema.indexes
WHERE table_name='CHILD';

-- there are now 2 indices: one for the PK, one for the FK

ALTER TABLE child DROP COLUMN name;

-- I could now drop the column

DROP TABLE child;

DROP TABLE parent;

-- now the invalid behavior

CREATE TABLE parent (
    id bigint PRIMARY KEY
);

CREATE TABLE child (
    id bigint PRIMARY KEY,
    fk bigint,
    name varchar(255),
    CONSTRAINT key_child UNIQUE (fk, name),
    CONSTRAINT fk_child FOREIGN KEY (fk) REFERENCES parent (id)
);

-- the foreign key constraint was created AFTER the unique constraint

SELECT index_type_name, index_name, column_name, sql 
FROM information_schema.indexes
WHERE table_name='CHILD';

-- there are 3 (!) indices: one for the PK, NONE for the FK and two for the 
unique constraint

ALTER TABLE child DROP CONSTRAINT key_child;

SELECT index_type_name, index_name, column_name, sql 
FROM information_schema.indexes
WHERE table_name='CHILD';

-- there are still 3 (!) indices, those for the unique constraint weren't 
removed

ALTER TABLE child DROP COLUMN name;

-- I cannot drop the name column

DROP TABLE child;

DROP TABLE parent;

What is the expected behavor?

It should make no difference in which order constraints are created.

What version of the product are you using? On what operating system, file 
system, and virtual machine?

1.3.166, Windows 7, JDK 1.6_u24

Do you know a workaround?

Change the order. But the Problem is, that this bug just causes an error if the 
table should be altered, and then it is too late :(

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

Our DB definition is done by Liquibase. There is no simple way to alter 
Liquibase-Files that were already inserted in the database. 

Original issue reported on code.google.com by manfred....@gmail.com on 14 May 2012 at 12:46

[GoogleCodeExporter]

Yes, drop constraint does not drop its index, if the index is still needed for 
another constraint.

> There is no simple way to alter Liquibase-Files

I don't think you need to alter the files, but instead couldn't you drop the 
constraint and re-create it, before dropping the name column? As follow:

ALTER TABLE child DROP CONSTRAINT fk_child;
ALTER TABLE child ADD CONSTRAINT fk_child FOREIGN KEY (fk) REFERENCES parent 
(id);
ALTER TABLE child DROP COLUMN name;

By the way, the rows in the table information_schema.indexes are index columns, 
not necessarily indexes.

-- there are 4 indices: one for the PK, one for the FK and two for the unique 
constraint

Actually, there are only 3 indexes: 
PRIMARY_KEY_3 on (id)
FK_CHILD_INDEX_3 on (fk)
KEY_CHILD_INDEX_3 on (fk, name)

-- there are 3 (!) indices: one for the PK, NONE for the FK and two for the 
unique constraint

Actually, there are only 2 indexes:
PRIMARY_KEY_3 on (id)
KEY_CHILD_INDEX_3 on (fk, name)

After dropping the key_child, there are still those 2 indexes, as an index is 
needed on "fk".

-- I cannot drop the name column

Yes, as there is still an index.

Original comment by thomas.t...@gmail.com on 29 Jul 2013 at 7:28

[GoogleCodeExporter]

I don't plan to change the current behavior right now. If you have a solution, 
patches are welcome, but as for myself I don't see an urgent need to change 
anything.

Original comment by thomas.t...@gmail.com on 19 Oct 2013 at 8:37

• Changed state: WontFix