This scenario is working with HSQLDB 2.0 too. ( This require to replace the legacy TEXT type to VARCHAR(254) ).
So, as expected, H2 accept the following insert because the foreign_key column value is NULL (no value): INSERT INTO users (id,name,profiles_id2) VALUES (1,'orphan_user',null); Next we insert a profile with a NULL id2, (this is accepted, the id2 column is unique but nullable). INSERT INTO profiles (id,id2,name) VALUES (3,null,'null_profile'); But we cannot remove that inserted profile: >Referential integrity constraint violation: "CONSTRAINT_4D4: PUBLIC.USERS >FOREIGN KEY(PROFILES_ID2) REFERENCES PUBLIC.PROFILES(ID2)"; SQL statement: >DELETE FROM profiles WHERE id2 IS NULL [23003-137] 23003/23003 I suspect that internally, H2 wrongly compare and considers that profiles.id2 (which is null) and users.profiles_id2 (which is null too) are equals. This may explain why the delete statement results with a constraint violation. Should i open a bug entry ? Thanks, 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.
