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 srgrocks <ma...@rediffmail.com> on 2011/08/12 11:41:51 UTC
Bug report for Apache Derby 10.4.x and onwards
Hello Apache Derby team ,
This is to bring to your kind attention the fact that Apache Derby 10.6.2.1 , the version I am using is giving a error on using the ON UPDATE CASCADE statement .
Here is the SQL statement in Java DB
create table "book"
(
Booking_ID varchar(255) primary key,
Customer_ID varchar(255) references "customer" ON UPDATE CASCADE ON DELETE CASCADE,
Hotel_ID varchar(255) references "hotel" ON UPDATE CASCADE ON DELETE CASCADE,
Date_Of_Booking varchar(255),
Check_In_Date varchar(255),
Check_Out_Date varchar(255),
No_Of_Rooms_Booked int ,
Total_Price varchar(255),
Status varchar(255)
)
The bug thrown is :
Error code -1, SQL state 42X01: Syntax error: Encountered "CASCADE" at line 4, column 58.
Line 1, column 1
Re: Bug report for Apache Derby 10.4.x and onwards
Posted by Rick Hillegas <ri...@oracle.com>.
Hello,
ON UPDATE CASCADE is not supported for REFERENCES clauses. If you follow
these links in the Reference Guide...
CREATE TABLE -> column-definition -> Column-level-constraint ->
REFERENCES clause
...you will find the following syntax description:
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}]
Hope this helps,
-Rick
On 8/12/11 2:41 AM, srgrocks wrote:
> Hello Apache Derby team ,
>
> This is to bring to your kind attention the fact that Apache Derby
> 10.6.2.1 , the version I am using is giving a error on using the ON
> UPDATE CASCADE statement .
>
> Here is the SQL statement in Java DB
>
> create table "book"
> (
> Booking_ID varchar(255) primary key,
> Customer_ID varchar(255) references "customer" *ON UPDATE CASCADE* ON
> DELETE CASCADE,
> Hotel_ID varchar(255) references "hotel" ON UPDATE CASCADE ON DELETE
> CASCADE,
> Date_Of_Booking varchar(255),
> Check_In_Date varchar(255),
> Check_Out_Date varchar(255),
> No_Of_Rooms_Booked int ,
> Total_Price varchar(255),
> Status varchar(255)
> )
>
> The bug thrown is :
>
> Error code -1, SQL state 42X01: Syntax error: Encountered "CASCADE" at
> line 4, column 58.
> Line 1, column 1
>
>
>
>
> <http://sigads.rediff.com/RealMedia/ads/click_nx.ads/www.rediffmail.com/signatureline.htm@Middle?>
> Treat yourself at a restaurant, spa, resort and much more with *Rediff
> Deal ho jaye!
> <http://track.rediff.com/click?url=___http://dealhojaye.rediff.com?sc_cid=mailsignature___&cmp=signature&lnk=rediffmailsignature&newservice=deals>*
Re: Bug report for Apache Derby 10.4.x and onwards
Posted by Kristian Waagan <kr...@oracle.com>.
On 12.08.11 11:41, srgrocks wrote:
> Hello Apache Derby team ,
>
> This is to bring to your kind attention the fact that Apache Derby
> 10.6.2.1 , the version I am using is giving a error on using the ON
> UPDATE CASCADE statement .
Hello,
ON UPDATE CASCADE is not supported for foreign keys.
From the Reference Manual under CREATE TABLE statement:
----
Foreign keys
If the generated column is part of a foreign key that references another
table, the referential action must not specify SET NULL or SET DEFAULT,
and the update rule must not specify ON UPDATE CASCADE.
----
I'm not sure if the documentation is accurate - I don't think ON UPDATE
CASCADE works even if no generated column is used.
To increase the chances of getting this feature implemented, you can
pick up or vote on DERBY-735 [1].
Regards,
--
Kristian
[1] https://issues.apache.org/jira/browse/DERBY-735
>
> Here is the SQL statement in Java DB
>
> create table "book"
> (
> Booking_ID varchar(255) primary key,
> Customer_ID varchar(255) references "customer" *ON UPDATE CASCADE* ON
> DELETE CASCADE,
> Hotel_ID varchar(255) references "hotel" ON UPDATE CASCADE ON DELETE
> CASCADE,
> Date_Of_Booking varchar(255),
> Check_In_Date varchar(255),
> Check_Out_Date varchar(255),
> No_Of_Rooms_Booked int ,
> Total_Price varchar(255),
> Status varchar(255)
> )
>
> The bug thrown is :
>
> Error code -1, SQL state 42X01: Syntax error: Encountered "CASCADE" at
> line 4, column 58.
> Line 1, column 1
>
>
>
< snip >