Unable to drop a column that has an associated foreign key constraint

[GoogleCodeExporter]

Before submitting a bug, please check the FAQ:
http://www.h2database.com/html/faq.html

What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1. Create 2 tables.

create table mytest (key bigint, another_column bigint)

create table mytest_foreign (key bigint)

2. Create a foreign key between mytest.key and mytest_foreign.key

alter table mytest add constraint mytest_foreign_fk foreign key(key) references 
mytest_foreign (key)

3. Try and drop mytest.key

alter table mytest drop column key

You then get error:

Column may be referenced by "PUBLIC.MYTEST_FOREIGN_FK"; SQL statement:
alter table mytest drop column key [90083-127] 90083/90083

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

This is ok as long as you know the name of the constraint but often times H2 
will create a constraint name such as:

FK33AFF27C9848DA

and the only way to find out is by interrogating the 
information_schema.constraints table which is convoluted and needlessly painful 
since a foreign key has no meaning if the column is being dropped.  It would be 
good if you could drop the column regardless of foreign key constraints, if 
existing behaviour must be maintained then the addition of a "force" keyword to 
the syntax would be ok to allow the user to indicate their intention.

Such as:

alter table mytest force drop column key

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

Version: h2-1.2.127
Operating System: Windows 7
Java Version: Java HotSpot(TM) Client VM (build 16.3-b01, mixed mode, sharing)

Do you know a workaround?

Interrogate the information_schema.constraints table, retrieve the constraint 
name and use that to drop the constraint(s).

select constraint_name from information_schema.constraints where table_name = ? 
and column_list = ?

How important/urgent is the problem for you?

Fairly urgent, I currently have columns that should really be dropped and I 
don't want to use the workaround if I can avoid it since it's messy.

In your view, is this a defect or a feature request?

Feature request.

Please provide any additional information below.

Original issue reported on code.google.com by g...@quollwriter.com on 12 Oct 2010 at 1:29

[GoogleCodeExporter]

Test case:

drop table mytest;
drop table mytest_foreign;
create table mytest (k bigint, another_column bigint);
create table mytest_foreign (k bigint primary key);
alter table mytest add constraint mytest_foreign_fk foreign key(k) references 
mytest_foreign (k);
alter table mytest drop column k;

This works on PostgreSQL and Apache Derby, but not on HSQLDB and H2.
In MySQL, I get: Error on rename of './test/#sql-10fdb_1' to './test/mytest' 
(errno: 150) HY000/1025

The workaround for H2 and HSQLDB is:

alter table mytest drop constraint mytest_foreign_fk;
alter table mytest drop column k;

Original comment by thomas.t...@gmail.com on 15 Oct 2010 at 6:36

[GoogleCodeExporter]

Hi,

What is your use case? Do you have an application, and as part of the 
application you want to drop a column (let's say to upgrade the schema to the 
next version of the application)? If yes why don't you just drop the constraint 
as well?

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 28 Jan 2011 at 7:44

[GoogleCodeExporter]

Yes I'm trying to drop a column on the schema upgrade.  As I mentioned in the 
original report though I don't know the name of the constraint, H2 has given it 
a name such as: FK33AFF27C9848DA.

It would be useful to just drop the column.

Gary

Original comment by bennsamg...@gmail.com on 28 Jan 2011 at 2:12

[GoogleCodeExporter]

I understand, so you created the constraint without specifying the constraint 
name, as in:

alter table child add foreign key(p) references parent(id);

I'm currently implementing a solution for this problem. There are other issues 
with the current algorithm, for example it thinks the column is referenced in 
some cases where it actually isn't.

The problem should be fixed in the next release.

However, what will still throw an exception is if the column is referenced by a 
multi-column referential constraint, check constraint, or index.

Original comment by thomas.t...@gmail.com on 29 Jan 2011 at 11:18

• Changed state: Accepted

[GoogleCodeExporter]

The changes are now committed in the trunk (but not all tests were ran so far).

Original comment by thomas.t...@gmail.com on 29 Jan 2011 at 3:56

[GoogleCodeExporter]

Fixed in version 1.3.151.

Original comment by thomas.t...@gmail.com on 12 Feb 2011 at 2:45

• Changed state: Fixed