John English created DERBY-7120:
-----------------------------------
Summary: Self-referential ON DELETE SET NULL
Key: DERBY-7120
URL: https://issues.apache.org/jira/browse/DERBY-7120
Project: Derby
Issue Type: Improvement
Components: SQL
Reporter: John English
Attachments: demo.sql
I have a table in which I want to include a self-referential foreign key to the
same table:
{code:java}
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
);{code}
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
The same code works well in other DBMSs (even MySQL!).
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
