Self-referential foreign key

2021-03-23 Thread John English 
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

Re: Self-referential foreign key

2021-03-23 Thread Rick Hillegas 
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,

Re: Self-referential foreign key

2021-06-28 Thread John English 

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

2021-06-28 Thread Rick Hillegas 

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

2021-06-28 Thread John English 

On 28/06/2021 17:59, Rick Hillegas wrote:

You're welcome to log an issue. Thanks.


Done.

--
John English