Referential integrity constraint violation when deleting records with FK relations to the same table

[Marcel Höhlig]

Hi, 

I'm currently facing problems with record deletion when using h2 version 1.4.200. To illustratate the issue, I've created the following test table:

-- Create Table: ENTRY
CREATE TABLE ENTRY
(
   entry_id            NUMBER(18) NOT NULL,
   collection_id       NUMBER(18) NOT NULL,
   parent_entry_id_fk  NUMBER(18)
);

ALTER TABLE ENTRY ADD CONSTRAINT ENTRY_PK PRIMARY KEY (entry_id);
   
ALTER TABLE ENTRY ADD CONSTRAINT ENTRY_FK_ENTRY FOREIGN KEY (parent_entry_id_fk)
   REFERENCES ENTRY (entry_id);

Afterwards I inserted two records where the second has a FK relation to the first record:

-- Insert 2 records 

INSERT INTO ENTRY (entry_id, collection_id, parent_entry_id_fk) VALUES (1,1,NULL);

INSERT INTO ENTRY (entry_id, collection_id, parent_entry_id_fk) VALUES (2,1,1);

Finally I tried to delete both records using the following statement:

-- Delete the inserted records by collection id

DELETE FROM ENTRY WHERE collection_id = 1

I got the following exception because the first record can't be deleted as it's referenced:

Referential integrity constraint violation: "ENTRY_FK_ENTRY: PUBLIC.ENTRY FOREIGN KEY(PARENT_ENTRY_ID_FK) REFERENCES PUBLIC.ENTRY(ENTRY_ID) (1)"; SQL statement:

-- Delete the inserted records by statement id

DELETE FROM ENTRY WHERE collection_id = 1 [23503-200] 23503/23503 (Help)

However, I'm still wondering if this is a bug as

• I thought the database will consider all records that should be deleted (because when marking all records for deletion, the fk relation does no longer exist).
• I tested the same statements with h2 version 1.4.199 without any issues.

Does anyone have the same issue or can confirm that this is a bug?

Thanks and regards

Marcel

[Noel Grandin]

we don't have "deferred" constraints, constraints are checked for every row, rather than at the end of the transaction.

So it's a case of a missing feature, but we're unlikely to implement it in the near future.

[Marcel Höhlig]

OK, thx. I was wondering why it's working with the previous version. Was this coincidence?