You're welcome to log an issue. Thanks.
On 6/28/21 6:30 AM, John English wrote:
On 23/03/2021 17:06, Rick Hillegas wrote:
This may help:
https://stackoverflow.com/questions/29297043/on-delete-set-null-on-self-referencing-relationship
I solved this with triggers, as suggested. Problem is that the column
can no longer be a foreign key.
But the other day I was playing around and found that this seems to
work without any problems in other DBMSs -- even the appalling MySQL!
Any change that someone will look at implementing this in Derby for
some forthcoming version?
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,
