db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Derby foreign key ON UPDATE clause problem
Date Thu, 06 Apr 2006 09:54:42 GMT
Glenn Marintes wrote:
> Thanks...
> 
> Now its clearer! :)

That's good :)

I read a little on the net. Although some database systems do support ON 
UPDATE CASCADE and it is an optional part of the SQL standard (as Bernt 
informed us), there are people that claim this feature should not be 
used. Their argument is that a primary key should not change; it should 
be constant. If you find the need to change it, it might be that you 
have selected the "wrong" candidate key to use as the primary key.

There are no definite rules here of course, but the motivation for this 
piece of advice seems to be that changing values of primary keys have 
caused a lot of trouble for people, especially since the support for 
this in database systems has been limited. This also has impact on 
portability, as Bernt mentioned.




--
Kristian

> 
>> Glenn Marintes wrote:
>>> Hi All,
>>>
>>> ij> CREATE TABLE Seal ( sealId BIGINT NOT NULL, sealFilename 
>>> VARCHAR(512), sealCode VARCHAR(64) NOT NULL, sealName VARCHAR(256) 
>>> NOT NULL, sealGroupFK BIGINT NOT NULL, PRIMARY KEY(sealId), FOREIGN 
>>> KEY (sealGroupFK) REFERENCES SealGroup (sealGroupId) ON UPDATE 
>>> CASCADE );
>>> ERROR 42X01: Syntax error: Encountered "CASCADE" at line 1, column 256.
>>>
>>>
>>> What does this error mean? How do we correct it?
>>>
>>
>> Hi Glenn,
>>
>> I'm not an expert on this, but a look in the reference manual tells me 
>> that you cannot use CASCADE with ON UPDATE.
>>
>> REFERENCES table-Name [ ( Simple-column-Name [ , Simple-column-Name ]* 
>> ) ]
>> [ ON DELETE {NO ACTION | RESTRICT | CASCADE | SET NULL}]
>>    [ ON UPDATE {NO ACTION | RESTRICT }]
>> |
>> [ ON UPDATE {NO ACTION | RESTRICT }] [ ON DELETE
>>    {NO ACTION | RESTRICT | CASCADE | SET NULL}]
>>
>>
>> As you can see, only NO ACTION or RESTRICT can be used with ON UPDATE.
>> Maybe someone can elaborate on why this is so?
>> Is this a piece of missing functionality in Derby?
>>
>>
>>
>>
>> -- 
>> Kristian
>>
> 
> 


Mime
View raw message