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 Glenn Marintes <gl...@janet.co.jp> on 2006/04/06 08:46:23 UTC
Derby foreign key ON UPDATE clause problem
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?
--
*******************************************
*
* Glenn M. Marintes
* Software Development Section
* JANET
*
Re: Derby foreign key ON UPDATE clause problem
Posted by Kristian Waagan <Kr...@Sun.COM>.
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
>>
>
>
Re: Derby foreign key ON UPDATE clause problem
Posted by Glenn Marintes <gl...@janet.co.jp>.
Thanks...
Now its clearer! :)
> 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
>
--
*******************************************
*
* Glenn M. Marintes
* Software Development Section
* JANET
*
Re: Derby foreign key ON UPDATE clause problem
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-04-06 10:40:46):
> Yes. SQL Feature "F701 Referential update actions" is only partially
> implemented. See http://wiki.apache.org/db-derby/SQLvsDerbyFeatures
>
> You could also provied some details in the "Note" field on the
> wiki-page.... ;-)
Another comment: F701 is neither part of SQL-99 Core nor SQL-2003
Mandatory features, so a portable application should probably not rely
on the feature.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: Derby foreign key ON UPDATE clause problem
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Kristian Waagan wrote (2006-04-06 10:27:13):
> 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?
Yes. SQL Feature "F701 Referential update actions" is only partially
implemented. See http://wiki.apache.org/db-derby/SQLvsDerbyFeatures
You could also provied some details in the "Note" field on the
wiki-page.... ;-)
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: Derby foreign key ON UPDATE clause problem
Posted by Kristian Waagan <Kr...@Sun.COM>.
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