Referential constraint not honored (or at least inconsistent) on DROP

[GoogleCodeExporter]

Consider the following script:

drop table if exists products;
drop table if exists categories;

CREATE TABLE categories(id INTEGER PRIMARY KEY, name VARCHAR(15) );
ALTER TABLE categories ADD CONSTRAINT Categories_unique_0 UNIQUE  (name);
INSERT INTO categories(id, name) VALUES(1, 'Beverages');

CREATE TABLE products(id INTEGER PRIMARY KEY, category VARCHAR(15), name 
VARCHAR(50));
ALTER TABLE products ADD CONSTRAINT Products_fkey_0 FOREIGN KEY  (category)  
REFERENCES Categories (name)  ON DELETE CASCADE;
INSERT INTO products(id, category, name) VALUES(1, 'Beverages', 'Chai');
INSERT INTO products(id, category, name) VALUES(2, 'Beverages', 'Chang');

drop table categories;

My expectation is that "drop table categories" will fail, but it does not and 
the table is removed.  I'm not sure if my expectation is correct, but I can say 
that this is inconsistent with the behavior of HSQL, Derby, MySQL, and 
PostreSQL where the table is kept and an SQLException is thrown.

I am using H2 1.3.168 and no options in my url: jdbc:h2:mem:iciql

Original issue reported on code.google.com by James.Mo...@gmail.com on 15 Oct 2012 at 5:48

[GoogleCodeExporter]

Hi,

According to my test, MySQL also allows to drop the table. So changing the 
behaviour would make H2 incompatible with MySQL. I think I will not change H2 
currently, even thought I understand it's incompatible with that most other 
databases do. But feel free to send a patch!

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 29 Oct 2012 at 7:10

[GoogleCodeExporter]

I'm testing against an old MySQL so perhaps this has changed.  But shouldn't 
this behavior be part of the MySQL compatibility mode instead?

Original comment by James.Mo...@gmail.com on 29 Oct 2012 at 7:14

[GoogleCodeExporter]

> But shouldn't this behavior be part of the MySQL compatibility mode instead?

Yes, that would be best. However, unfortunately I will not have time to 
implement 100% compatibility with all databases for all features. If you want 
to implement the feature please feel free.

Original comment by thomas.t...@gmail.com on 29 Oct 2012 at 8:19

[GoogleCodeExporter]

Original comment by noelgrandin on 6 Mar 2013 at 5:07

• Changed state: PatchesWelcome