Delete with cascade...

[Preben Mikael Bohn]

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

[Sylvain Leroux]

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

[Preben Mikael Bohn]

Hi Sylvain

> 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:

OK, I see...

The problem though is that I don't know the structure of the tables
and I need to do a batch delete of several tables. I could of course
go through each of the tables and manually find dependent tables but
since I have to do this over and over again during development where
the table structure changes significantly (automatically generated by
Hibernate) this is really not an option...

On the other hand I could go over all the tables and try to delete
them in a loop until all data are deleted (although some delete
statements would still fail)... That would probably do the trick...
:-)

Best regards Preben

[Derby-3]

> -----Original Message-----
> From: Preben Mikael Bohn [mailto:preben@...]
> Sent: Thursday, October 22, 2009 7:18 AM
> To: Derby Discussion
> Subject: Re: Delete with cascade...
>
>
> The problem though is that I don't know the structure of the tables
> and I need to do a batch delete of several tables. I could of course
> go through each of the tables and manually find dependent tables but
> since I have to do this over and over again during development where
> the table structure changes significantly (automatically generated by
> Hibernate) this is really not an option...
>
> On the other hand I could go over all the tables and try to delete
> them in a loop until all data are deleted (although some delete
> statements would still fail)... That would probably do the trick...
> :-)
>
> Best regards Preben

First, don't use Hibernate. Sorry, but from practice a well written DAO will
be better performance-wise. Maybe look at JPA?

Second, remember that meta-data is your friend.
You don't know much about your table structures, but Derby, the relational
database engine does.

You should be able to query and find these constraints. Then you can
programmatically determine which tables to delete records from and in which
order.

Remember that meta data is your friend.
Oh wait, I already said that. But really, it is. Even if you use hibernate,
the logic to walk through the metadata won't change even if the underlying
tables do.

HTH

-Mikey

[Preben Mikael Bohn]

Hi Mikey

> First, don't use Hibernate. Sorry, but from practice a well written DAO will
> be better performance-wise. Maybe look at JPA?

Well, since Hibernate implements JPA that's exactly what I do... ;-)

> You should be able to query and find these constraints. Then you can
> programmatically determine which tables to delete records from and in which
> order.

Yes, that's true... I guess I just hoped for something simple as
postgres' truncate/cascade... :-)

Best regards Preben

[Michael Segel]

Like I said, I roll my own DAO because its always going to be more
efficient, if done properly. ;-)

With respect to ease of use, I'm confused.

You only need to figure out the metadata once. It won't change, if rarely.
Then you can reuse it all the time.

All databases (relational databases) have to have some form of metadata and
metadata access so once you figure out the logic for a database, you can
easily port the logic to any other relational database.

Then you don't have to worry if that vendor supports truncate/cascade or how
they support it.

I mean derby does support the concept but its done differently.

HTH

-M

> -----Original Message-----
> From: Preben Mikael Bohn [mailto:preben@...]
> Sent: Thursday, October 22, 2009 1:20 PM
> To: Derby Discussion
> Subject: Re: Delete with cascade...
>
> Hi Mikey
>
> > First, don't use Hibernate. Sorry, but from practice a well written DAO
> will
> > be better performance-wise. Maybe look at JPA?
>
> Well, since Hibernate implements JPA that's exactly what I do... ;-)
>
> > You should be able to query and find these constraints. Then you can
> > programmatically determine which tables to delete records from and in
> which
> > order.
>
> Yes, that's true... I guess I just hoped for something simple as
> postgres' truncate/cascade... :-)
>
> Best regards Preben

[Sylvain Leroux]

I'm not a specialist of Hibernate, but there are some "cascade" options that you
might find useful. Something like that:
<set name="children" inverse="true" cascade="save-update">
   <key name="PARENT_ID" on-delete="cascade">
   <one-to-many class="Child">
<set>
(please note the cascade="save-update" instead of cascade="all")

According to
http://eddii.wordpress.com/2006/11/16/hibernate-on-deletecascade-performance/,
with these settings Hibernate will efficiently use "ON DELETE CASCADE" at DB
level. Without requiring to load all the objects to be deleted in the
application first.


I don't know if Hibernate is able to use this feature this with Derby. If you
find out, please, let us know!

Sylvain.


Preben Mikael Bohn a écrit :

> Hi Sylvain
>
>> 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:
>
> OK, I see...
>
> The problem though is that I don't know the structure of the tables
> and I need to do a batch delete of several tables. I could of course
> go through each of the tables and manually find dependent tables but
> since I have to do this over and over again during development where
> the table structure changes significantly (automatically generated by
> Hibernate) this is really not an option...
>
> On the other hand I could go over all the tables and try to delete
> them in a loop until all data are deleted (although some delete
> statements would still fail)... That would probably do the trick...
> :-)
>
> Best regards Preben
>
>

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