[DERBY-7120] Self-referential ON DELETE SET NULL

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: SQL
• Urgency: Normal
• Assignee: Unassigned
• Reporter: John English
• Created: 28/Jun/21 15:18
• Updated: 28/Jun/21 15:49

Description

I have a table in which I want to include a self-referential foreign key to the same table:

CREATE TABLE x (
  id   INTEGER PRIMARY KEY
);

CREATE TABLE y (
  id   INTEGER PRIMARY KEY,
  id_x INTEGER,
  id_y INTEGER,
  CONSTRAINT y1 FOREIGN KEY (id_x)
                REFERENCES x(id)
                ON DELETE CASCADE,
  CONSTRAINT y2 FOREIGN KEY (id_y)
                REFERENCES y(id)
                ON DELETE SET NULL
);

When I try this I get the following error:

Error: Foreign Key 'Y2' is invalid because 'The delete rule of foreign key must be CASCADE. (The referential constraint is self-referencing and the table is dependent in a relationship with a delete rule of CASCADE.)'.
SQLState: 42915
ErrorCode: 30000

I have worked around this for now by removing the foreign key constraint and using a trigger:

CREATE TRIGGER set_null
  AFTER DELETE ON y
  REFERENCING OLD AS del
  FOR EACH ROW MODE DB2SQL
  UPDATE y SET id_y = NULL WHERE id_y = del.id;

The problem is that id_y is no longer constrained to be a foreign key, and other approaches, e.g. CHECK (id_y IN (SELECT id FROM y)), don't work either.

The same code works well in other DBMSs (even MySQL!). Demo code is attached.