Hi, During some tests to well-understand the behaviour of foreign keys, i suspect having found a bug with nullable foreign keys where the referenced column is unique but nullable: Unsure if it is a bug or something that is imprecise in the SQL standard, however the H2 behaviour is different from POSTGRESQL.
H2 version: ======== 1.2.137 Test case: ========== CREATE TABLE profiles ( id BIGINT PRIMARY KEY, id2 BIGINT UNIQUE, name TEXT ); CREATE TABLE users ( id BIGINT PRIMARY KEY, name TEXT, profiles_id2 BIGINT, FOREIGN KEY(profiles_id2) REFERENCES profiles(id2) ); INSERT INTO users (id,name,profiles_id2) VALUES (1,'orphan_user',null); INSERT INTO profiles (id,id2,name) VALUES (3,null,'null_profile'); DELETE FROM profiles WHERE id2 IS NULL; Under POSTGRESQL, the DELETE statement is accepted. However, under H2, it failed, it seems that H2 establish a relationship with null value on the foreign key.. Regards, Cédric. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
