db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Self-referential foreign key
Date Tue, 23 Mar 2021 15:06:00 GMT
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: 30000
>
> 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,



Mime
View raw message