[
https://issues.apache.org/jira/browse/DERBY-7120?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
John English updated DERBY-7120:
--------------------------------
Description:
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 PRIMARY KEY
);
CREATE TABLE y (
id INTEGER PRIMARY KEY,
id_x INTEGER,
id_y INTEGER,
CONSTRAINT x1 FOREIGN KEY (id_x)
REFERENCES x(id)
ON DELETE CASCADE,
CONSTRAINT x2 FOREIGN KEY (id_y)
REFERENCES y(id)
ON DELETE SET NULL
);
{code}
When I try this I get the following error:
Error: Foreign Key 'X2' 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
I have worked around this for now by removing the foreign key constraint and
using a trigger:
{code:java}
CREATE TRIGGER set_null
AFTER DELETE ON y
REFERENCING OLD AS del
FOR EACH ROW MODE DB2SQL
UPDATE y SET id_y = NULL WHERE id_y = del.id;
{code}
The problem is that id_y is no longer constrained to be a foreign key, and
other approaches (e.g. CHECK(id_y IN (SELECT id FROM y))) don't work either.
The same code works well in other DBMSs (even MySQL!). Demo code is attached.
was:
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 PRIMARY KEY
);
CREATE TABLE y (
id INTEGER PRIMARY KEY,
id_x INTEGER,
id_y INTEGER,
CONSTRAINT x1 FOREIGN KEY (id_x)
REFERENCES x(id)
ON DELETE CASCADE,
CONSTRAINT x2 FOREIGN KEY (id_y)
REFERENCES y(id)
ON DELETE SET NULL
);
{code}
When I try this I get the following error:
Error: Foreign Key 'X2' 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
I have worked around this for now by removing the foreign key constraint and
using a trigger:
{code:java}
CREATE TRIGGER set_null
AFTER DELETE ON y
REFERENCING OLD AS del
FOR EACH ROW MODE DB2SQL
UPDATE y SET id_y=NULL WHERE id_y=del.id;
{code}
The problem is that id_y is no longer constrained to be a foreign key, and
other approaches (e.g. CHECK(id_y IN (SELECT id FROM y))) don't work either.
The same code works well in other DBMSs (even MySQL!). Demo code is attached.
> 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
> Priority: Major
> 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 PRIMARY KEY
> );
> CREATE TABLE y (
> id INTEGER PRIMARY KEY,
> id_x INTEGER,
> id_y INTEGER,
> CONSTRAINT x1 FOREIGN KEY (id_x)
> REFERENCES x(id)
> ON DELETE CASCADE,
> CONSTRAINT x2 FOREIGN KEY (id_y)
> REFERENCES y(id)
> ON DELETE SET NULL
> );
> {code}
> When I try this I get the following error:
> Error: Foreign Key 'X2' 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
> I have worked around this for now by removing the foreign key constraint and
> using a trigger:
> {code:java}
> CREATE TRIGGER set_null
> AFTER DELETE ON y
> REFERENCING OLD AS del
> FOR EACH ROW MODE DB2SQL
> UPDATE y SET id_y = NULL WHERE id_y = del.id;
> {code}
> The problem is that id_y is no longer constrained to be a foreign key, and
> other approaches (e.g. CHECK(id_y IN (SELECT id FROM y))) don't work either.
> The same code works well in other DBMSs (even MySQL!). Demo code is attached.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
