Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-7120

Self-referential ON DELETE SET NULL

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None
    • Urgency:
      Normal

      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.

        Attachments

        1. demo.sql
          0.6 kB
          John English

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              Je_suis John English
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: