Open GoogleCodeExporter opened 9 years ago
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
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
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
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
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
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
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
OK, I re-open this issue, and will accept patches.
Original comment by thomas.t...@gmail.com
on 21 Nov 2013 at 11:32
Original issue reported on code.google.com by
a...@futura4.org
on 2 Nov 2011 at 11:03