db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrej Tibaut <andrej.tib...@uni-mb.si>
Subject RE: ON UPDATE CASCADE
Date Tue, 05 Apr 2005 08:16:02 GMT
This is true, it's RESTRICT by default
 
In my example below I use default cascade values for ON DELETE RESTRICT and
ON UPDATE RESTRICT:
   FOREIGN KEY (VrstaId) REFERENCES VrstaPrevoza(VrstaId);

 
In the manual
(http://incubator.apache.org/derby/manuals/reference/sqlj32.html#HDRSII-SQLJ
-16357)
the options for ON UPDATE are NO ACTION and RESTRICT, but I would need ON
UPDATE CASCADE: 
   FOREIGN KEY (VrstaId) REFERENCES VrstaPrevoza(VrstaId) ON UPDATE CASCADE;
 
This is possible in PostgreSQL but obviously not in Derby. How do other
users handle cascade updates in Derby?
I think that ON UPDATE CASCADE is a good candidate for new feature in
Derby?!
 
 
Ideas, comments!
 
Andrej
 

 



  _____  

From: Arda M [mailto:mardacay@yahoo.com] 
Sent: 5. april 2005 4:31
To: Derby Discussion; andrej.tibaut@uni-mb.si
Subject: Re: ON UPDATE CASCADE


While creating the table, you need to specify what cascade options you want
for your foreign keys. It is "restrict" by default i guess.


Andrej Tibaut <andrej.tibaut@uni-mb.si> wrote: 

I have setup the following database. It contains two tables with two columns
each. The first table (VrstaPrevoza) is a parent table to the second one
(VozniRedi) via foreign key on field VrstaId. I want the changes (updates)
to the VrstaPrevoza.VrstaId to be propagated to the child table VozniRedi. 
The UPDATE statement results in an error.
 
------------------------
 
My code:
 
DRIVER 'org.apache.derby.jdbc.EmbeddedDriver';
CONNECT 'jdbc:derby:database/test;create=true';

-- VrstaPrevoza
DROP TABLE VrstaPrevoza;
CREATE TABLE VrstaPrevoza (VrstaId CHAR(2) NOT NULL, 
                           Opis VARCHAR(20) NOT NULL, 
                          PRIMARY KEY (VrstaId));
INSERT INTO VrstaPrevoza VALUES ('MK','bla bla');
INSERT INTO VrstaPrevoza VALUES ('PR','bla bla');
INSERT INTO VrstaPrevoza VALUES ('MN','bla bla');
 
-- VozniRedi
DROP TABLE VozniRedi;
CREATE TABLE VozniRedi (VozniRedId VARCHAR(50) NOT NULL, 
   VrstaId CHAR(2)  NOT NULL,
                    PRIMARY KEY (VozniRedId),
   FOREIGN KEY (VrstaId) REFERENCES VrstaPrevoza(VrstaId));
INSERT INTO VozniRedi VALUES ('MK1110010R','MK');
INSERT INTO VozniRedi VALUES ('MK1110020R','MK');
 
 
 
ij> UPDATE VrstaPrevoza SET VrstaId='AB' WHERE VrstaId='MK';
ERROR 23503: UPDATE on table 'VRSTAPREVOZA' caused a violation of foreign
key co
nstraint 'SQL050403042438881' for key (MK).  The statement has been rolled
back.
 
-------------------------------
 

I would expect to be able to define the foreign key like
FOREIGN KEY (VrstaId) REFERENCES VrstaPrevoza(VrstaId) ON UPDATE CASCADE;
but this doesn't work in Derby.
 
How to implement "UPDATE CASCADE"??
 
Many thanks!
 
Andrej



  _____  

Yahoo! Messenger
Show us what our next emoticon should look like. Join
<http://us.rd.yahoo.com/evt=31855/*http://advision.webevents.yahoo.com/emoti
contest> the fun.


Mime
View raw message