This may help:
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,
idX INTEGER DEFAULT NULL,
CONSTRAINT x_pk PRIMARY KEY (id),
CONSTRAINT x_1 FOREIGN KEY (idY)
ON DELETE CASCADE,
CONSTRAINT x_2 FOREIGN KEY (idX)
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.)'.
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?