Cascading Deletes/Updates using JPA or Inside of Database?

Question

Performance is key: Is it better to cascade deletes/updates inside of the Database or let Hibernate/JPA take care of it?

Will this effect the ability to query for the data if cascades are inside of the DBMS?

I am using HSQLDB if that matters.

Answer 1

1. Correctness is key

That said in this instance correctness and performance will almost certainly go hand in hand since the database is the right place to put (and enforce) hard constraints on data integrity and it will be measurably faster on a serious delete cascade since it:

• Avoids multiple roundtrips to the database
• Reduces the time during which a transaction may have to be held
• Can make use of internal structures related to the foreign key index to do it without having to workout/reuse some execution plan

Answer 2

UPDATE: looks like similar question already was answered here http://stackoverflow.com/questions/59297/whenwhy-to-use-cascading-in-sql-server

IMO correct answer on your question will be as usual "it depends". If you are using a database as a storage of sensitive information (e.g. financial, medical etc.), or if someone else outside of your application could have access to the database I will vote for Hibernate/JPA approach. If your database is for logging (e.g. web site traffic etc.) or if you are developing software with embedded database you can relatively safely use cascade operations.

2. In most cases I will vote for Hibernate/JPA approach because it's more manageable and predictable.

I tell you a story. Some times ago young country decided to change national currency (it happened with young countries). Couple years later new DBA saw in a currency table row with obsolete currency and decided to delete it (who knows why). Guess what happened? 30% of database was deleted because of cascade deletion operations. IMO with cascade operations you have to be super careful with all you delete/update statements from one hand and you lose power of constrains (i.e. foreign keys) for database validation from other hand.

Answer 3

In the case of cascading updates, you simply cannot do it in application space if you have foreign key constraints in the database.

Example: say you have a lookup table for US states, with a primary key of the two-letter abbreviation. Then you have a table for mailing addresses that references it. Someone tells you that you mistakenly gave Montana the abbreviation "MO" instead of "MT" so you need to change it in the lookup table.

CREATE TABLE States (st CHAR(2) PRIMARY KEY, state VARCHAR(20) NOT NULL);
INSERT INTO States VALUES ('MO', 'Montana');

CREATE TABLE Addresses (addr VARCHAR(20), city VARCHAR(20), st CHAR(2), zip CHAR(6),
  FOREIGN KEY (st) REFERENCES States(st));
INSERT INTO Addresses VALUES ('1301 East Sixth Ave.', 'Helena', 'MO', '59620');

Now you go to fix the mistake, without the aid of database-side cascading updates. Below is a test using MySQL 5.0 (assume no records exist for Missouri, which actually does use the abbreviation "MO").

UPDATE States SET st = 'MT' WHERE st = 'MO';

ERROR 1451 (23000): Cannot delete or update a parent row: 
 a foreign key constraint fails (`test/addresses`, 
 CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`st`) REFERENCES `states` (`st`))

UPDATE Addresses SET st = 'MT' WHERE st = 'MO';

ERROR 1452 (23000): Cannot add or update a child row: 
 a foreign key constraint fails (`test/addresses`, 
 CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`st`) REFERENCES `states` (`st`))

UPDATE Addresses JOIN States USING (st)
SET Addresses.st = 'MT', States.st = 'MT'
WHERE States.st = 'MO';

ERROR 1451 (23000): Cannot delete or update a parent row: 
 a foreign key constraint fails (`test/addresses`, 
 CONSTRAINT `addresses_ibfk_1` FOREIGN KEY (`st`) REFERENCES `states` (`st`))

No application-side query can solve this situation. You need cascading updates in the database in order to perform the update in both tables atomically, before the referential integrity constraint is enforced.