db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: delete-connection SET NULL: two columns in one table referencing same column
Date Thu, 11 Feb 2010 10:22:52 GMT
Patrick Mueller <paddy.m@gmx.net> writes:

> noone? :(
>
> Am 02.02.2010 17:08, schrieb Patrick Mueller:
>> Hi derby users!
>>
>> In my database I want two columns referencing as FK to the same
>> column in another table(see below) using the delete connection SET
>> NULL but it just won't work.
>>
>> ALTER TABLE HM_DESK ADD CONSTRAINT DESK_OWNER_FK FOREIGN KEY
>> (IDF_OWNER) REFERENCES HM_USER (ID_USER) ON DELETE SET NULL;
>> ALTER TABLE HM_DESK ADD CONSTRAINT DESK_LASTEDITEDBY_FK FOREIGN KEY
>> (IDF_LASTEDITEDBY) REFERENCES HM_USER (ID_USER) ON DELETE SET NULL;
>>
>> --> results in a error when executing the second statement:
>>
>> "Error: Foreign  Key 'DESK_LASTEDITEDBY_FK' is invalid because 'The
>> table cannot be defined as a dependent of table APP.HM_USER because
>> of delete rule restrictions. (The relationship causes the table to
>> be delete-connected to the indicated table through multiple
>> relationships and the delete rule of the existing relationship is
>> SET NULL.).  '.
>> SQLState:  42915
>> ErrorCode: 30000"
>>
>> if I am using 'ON DELETE CASCADE'  it works perfectly fine.. what's
>> wrong?

Hi Patrick,

The code that generates the exception has this comment:

//more than one  ON DELET SET NULL to the same table is not allowed

However, I don't know why there is such a restriction, and I cannot find
that it's mentioned in the manuals.

-- 
Knut Anders

Mime
View raw message