db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Sylvain Leroux <s...@wanadoo.fr>
Subject Re: Delete with cascade...
Date Thu, 22 Oct 2009 11:04:03 GMT
Hi Preben,

Derby supports cascade delete as part of the referential action specified at 
table's creation time.
Then, when using a DELETE statement to remove row(s) from the referenced table, 
the DELETE operation is propagated to the dependent table. Here is an example:

CREATE TABLE dest (
     id int,
     PRIMARY KEY(id)
);

CREATE TABLE source (
     id int GENERATED ALWAYS AS IDENTITY,
     ref int REFERENCES dest(id) ON DELETE CASCADE,
     PRIMARY KEY(id)
);

INSERT INTO dest VALUES (1), (2), (3);
INSERT INTO source(ref) VALUES (1), (1), (3);


DELETE FROM dest WHERE id = 1;
SELECT * FROM source;
--> will return only the row (id:3, ref:3)



Hope this helps,
Sylvain

Preben Mikael Bohn a écrit :
> Hi all
> 
> I'm trying to delete all entries from a bunch of tables in my
> database; from the reference manual it seems that the DELETE statement
> cannot do cascading operations. So when I try to delete tables where I
> have foreign key constraints I get an error like
> 
> "DELETE on table 'foo caused a violation of foreign key constraint
> 'bar' for key (196608).  The statement has been rolled back."
> 
> In postgres I simply do "TRUNCATE TABLE tablename CASCADE"; is there
> something similar in Derby?
> 
> Best regards Preben
> 
> 


-- 
Website: http://www.chicoree.fr



Mime
View raw message