This may help:
https://stackoverflow.com/questions/29297043/on-delete-set-null-on-self-referencing-relationship
On 3/23/21 6:09 AM, John English wrote:
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,
