[
https://issues.apache.org/jira/browse/DERBY-7120?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Richard N. Hillegas updated DERBY-7120:
---------------------------------------
Urgency: Normal
> 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 y1 FOREIGN KEY (id_x)
> REFERENCES x(id)
> ON DELETE CASCADE,
> CONSTRAINT y2 FOREIGN KEY (id_y)
> REFERENCES y(id)
> ON DELETE SET NULL
> );
> {code}
> When I try this I get the following error:
> Error: Foreign Key 'Y2' 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)
