NO ACTION should be different from RESTRICT

[Sanadis]

We have a couple of foreign keys in our database that reference another record within the same table. As of release 1.4.200, if we attempt to delete records in which the first record is referenced by the second record, a Referential integrity constraint violation will occur. We were previously using 1.4.199 and this was not an issue, and I would not expect this to ever be an issue as the DBMS is typically designed to handle this scenario.

Here is the query I used to test with (Ran through the H2 web client):

DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(
  ID INT PRIMARY KEY,
  NAME VARCHAR(255),
  UPLINEID INT,
  FOREIGN KEY (UPLINEID) REFERENCES TEST(ID)
);

INSERT INTO TEST VALUES(1, 'A', NULL); 
INSERT INTO TEST VALUES(2, 'A', 1);

DELETE FROM TEST WHERE NAME='A';

DROP TABLE IF EXISTS TEST;
Update count: 0
(1 ms)

CREATE TABLE TEST(
  ID INT PRIMARY KEY,
  NAME VARCHAR(255),
  UPLINEID INT,
  FOREIGN KEY (UPLINEID) REFERENCES TEST(ID)
);
Update count: 0
(3 ms)

INSERT INTO TEST VALUES(1, 'A', NULL);
Update count: 1
(0 ms)
 
INSERT INTO TEST VALUES(2, 'A', 1);
Update count: 1
(0 ms)

DELETE FROM TEST WHERE NAME='A';
Referential integrity constraint violation: "CONSTRAINT_27: PUBLIC.TEST FOREIGN KEY(UPLINEID) REFERENCES PUBLIC.TEST(ID) (1)"; SQL statement:
DELETE FROM TEST WHERE NAME='A' [23503-200] 23503/23503 (Help)

ID 2 uses the UPLINEID to reference ID 1. I assume what is happening is that the delete is just sequentially attempts to delete the first first and then the second.

Edit:
I have also tested with using CASCADE on the constraint:
FOREIGN KEY (UPLINEID) REFERENCES TEST(ID) ON DELETE CASCADE

This does allow it to work, although this would make the behavior of H2 different than SQL Server, Oracle, etc since they handle this fine without cascading.

[katzyn]

The SQL Standard determines behavior in the following way:

If a <referential constraint definition> does not specify any <delete rule>, then a <delete rule> with a <referential action> of NO ACTION is implicit.

A <delete rule> that does not specify NO ACTION specifies a referential delete action. Referential update actions and referential delete actions are collectively called referential actions. Referential actions are carried out before, and are not part of, the checking of a referential constraint.

ON DELETE NO ACTION (the default): there is no referential delete action; the referential constraint only specifies a constraint check.
NOTE — Even if constraint checking is not deferred, ON DELETE RESTRICT is a stricter condition than ON DELETE NO ACTION. ON DELETE RESTRICT prohibits a delete of a particular row if there are any matching rows; ON DELETE NO ACTION does not perform its constraint check until the entire set of rows to be deleted has been processed.

H2 incorrectly treats the default and explicit NO ACTION in the same way as RESTRICT.

[longstone]

@katzyn many thanks for your work!
Do you have plans to fix this problem? And if so, can you already give a possible (next release) date?

[katzyn]

Sorry, I don't have enough time for this problem.

H2 is developed by its community and contributions are welcome, as usual. But this issue isn't trivial and may require good knowledge of H2's internals.