db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Self-referential foreign key
Date Tue, 23 Mar 2021 13:09:27 GMT
I have a table in which I want to include a self-referential foreign key 
to the same table:

   CREATE TABLE x (
     id  INTEGER  GENERATED ALWAYS AS IDENTITY,
     idY INTEGER,
     idX INTEGER DEFAULT NULL,
     CONSTRAINT x_pk   PRIMARY KEY (id),
     CONSTRAINT x_1    FOREIGN KEY (idY)
                       REFERENCES y(id)
                       ON DELETE CASCADE,
     CONSTRAINT x_2    FOREIGN KEY (idX)
                       REFERENCES x(id)
                       ON DELETE SET NULL
   );

When I try this I get the following error:

Error: Foreign  Key 'X_2' 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

My intention is that when rows from table Y are deleted, corresponding 
rows from X are also deleted, but when rows from X are deleted, the 
references in any referencing rows in X are just set to NULL.

I don't understand exactly what I'm doing wrong here. How can I rewrite 
the table definition to do what I want?

Thanks,
-- 
John English

Mime
View raw message