[jira] [Updated] (DERBY-7120) Self-referential ON DELETE SET NULL

["John English (Jira)" <ji...@apache.org>]

     [ 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 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 '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!).

  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 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!).


> 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 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 '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)