You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by John English <jo...@gmail.com> on 2021/03/23 13:09:27 UTC
Self-referential foreign key
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,
--
John English
Re: Self-referential foreign key
Posted by John English <jo...@gmail.com>.
On 28/06/2021 17:59, Rick Hillegas wrote:
> You're welcome to log an issue. Thanks.
Done.
--
John English
Re: Self-referential foreign key
Posted by Rick Hillegas <ri...@gmail.com>.
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: 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,
>>
>>
>
>
Re: Self-referential foreign key
Posted by John English <jo...@gmail.com>.
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: 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,
>
>
--
John English
Re: Self-referential foreign key
Posted by Rick Hillegas <ri...@gmail.com>.
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,