Re: Self-referential foreign key
On 28/06/2021 17:59, Rick Hillegas wrote: You're welcome to log an issue. Thanks. Done. -- John English
Re: Self-referential foreign key
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: 3 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,
Re: Self-referential foreign key
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: 3 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
Re: Self-referential foreign key
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: 3 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,
Self-referential foreign key
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_1FOREIGN KEY (idY) REFERENCES y(id) ON DELETE CASCADE, CONSTRAINT x_2FOREIGN 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: 3 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
