Foreign key is hijacking user created index

[GoogleCodeExporter]

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

1. create two tables "A" and "B" both with a column "C"
2. CREATE INDEX "Foo" ON "A" ("C")
3. ALTER TABLE "Foo" ADD CONSTRAINT "Bar" FOREIGN KEY ("C") REFERENCES "B" ("C")
4. DROP INDEX "Foo"

What is the expected output? What do you see instead?

This should work without any errors, cause index "Foo" was created by my self 
and there is no explizit defined relationship between index "Foo" and foreign 
key "Bar".

But I get the following error:

Error: Index "Blah" belongs to a constraint; SQL statement:
DROP INDEX "Blah" [90085-161]
SQLState:  90085
ErrorCode: 90085

If I create the foreign key "Bar" first and then the index "Foo", everything 
works fine.

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

H2 1.3.161

Do you know a workaround?

No, because I cannot control the order of creating of indices and foreign keys.

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

Schema update tool

How important/urgent is the problem for you?

For me: very urgent

Please provide any additional information below.

The error analyze says:
The error with code 90085 is thrown when trying to manually drop an index that 
was generated by the system because of a unique or referential constraint.

The problem is not, that the foreign key uses the index, but the index was 
created by myself and not by the system, so I expect to be able to drop it 
again.

May be the index should just be renamed to some internal name, when it is to be 
dropped.

Original issue reported on code.google.com by a...@futura4.org on 2 Nov 2011 at 11:03

[GoogleCodeExporter]

Line 3 should be:
ALTER TABLE "A" ADD CONSTRAINT "Bar" FOREIGN KEY ("C") REFERENCES "B" ("C")

Original comment by a...@futura4.org on 2 Nov 2011 at 11:09

[GoogleCodeExporter]

Hi,

You are right, H2 doesn't let you drop the index in this case. According to my 
test, MySQL works the same way. Other databases let you drop the index without 
problems. Test case:

drop table test cascade;
create table test(id int primary key, parent int);
create index parent_idx on test(parent);
alter table test add constraint x foreign key(parent) references test(id);

-- PostgreSQL, HSQLDB, Derby: works
-- H2: fails (index is used)
-- MySQL: doesn't work (invalid syntax)
drop index parent_idx;

-- MySQL 5.1.47: fails with
-- Error on rename of './test/#sql-1986_4' to './test/test' (errno: 150) 
HY000/1025
drop index parent_idx on test;

I agree it's quite unfortunate and unexpected behavior. I would like to better 
understand what impact this problem has.

No, because I cannot control the order of creating of indices and foreign keys.

Do you use a tool that creates the database objects? If yes, which one?

Why do you need to drop the index? The constraint needs an index anyway, so you 
might just keep it.

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 6 Nov 2011 at 10:33

[GoogleCodeExporter]

Hi,

I'm just writing that update tool. Its job is to change the schema of a given 
database in a way that it conforms a given dictionary. That dictionary is a 
part of our product and describes a database schema independent from a special 
DBMS. Beside tables and table columns it defines indices and foreign keys.

Therefore the update tool has to meet some challenges. One is that I don't know 
the exact state of the database, when it is started (it has to explore it). The 
other thing is, the tool must be able to work with different DBMS (especially: 
H2, Derby, PostgreSQL, MSSQL).

In case of H2 I currently need to drop all foreign keys before I can update the 
indices, cause one of the indices in the database may not exists in the 
dictionary anymore but is still used by a foreign key. And I see no way to 
recognize if an index is still used by a foreign key when it does not have a 
significant name.

But rebuilding of foreign keys needs time if there are a million records or 
more. So I would like to keep foreign keys that needn't to be changed.

regards
Axel

Original comment by a...@futura4.org on 7 Nov 2011 at 9:54

[GoogleCodeExporter]

Hi

Once you want to support MySQL then you need this solution :-)

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 17 Nov 2011 at 7:15

[GoogleCodeExporter]

This issue might be related to the problem I described here:
http://groups.google.com/group/h2-database/t/cdef82a168babed5

I found it very reasonable that H2 uses a matching index for a FK instead of 
creating a redundant one.
The unfortunate part begins when you cannot safely determine all the FKs using 
a certain index to have a chance to programmatically "do" something about it.

For you particular situation with your update tool, this would reduce the 
number of foreign keys to be dropped from "all" to only those that use the 
indices.

On a side note:
That "update tool" sounds like a subset of my hobby project:
A application-side DBMS-independent SQL abstraction layer, fluent-code-ly 
representing SQL statements and their execution and defining and synchronizing 
DB schema definitions (tables, column, indices, constraints, etc).
Too bad I don't have a website or documentation yet to show it around :-/.

Original comment by Jadoth.P...@googlemail.com on 10 Dec 2011 at 9:57

[GoogleCodeExporter]

I don't plan to fix this problem as MySQL behaves in the same way. (you can 
call me lazy if you want :-)

Original comment by thomas.t...@gmail.com on 15 Dec 2011 at 10:06

• Changed state: WontFix

[GoogleCodeExporter]

I just hit this today. I'm using Oracle and SQL Server for deployment + H2 for 
fast-testing... And don't understand why H2 would refuse to drop my index where 
Oracle (and even SQL Server) just do what is expected.

Original comment by piotr.fi...@gmail.com on 22 Aug 2013 at 8:03

[GoogleCodeExporter]

OK, I re-open this issue, and will accept patches.

Original comment by thomas.t...@gmail.com on 21 Nov 2013 at 11:32

• Changed state: PatchesWelcome