CASCADE for delete and update

#11

On 14.08.2010 14:37, Thomas Kellerer wrote:

Quote:

The point is that David's assessment that manual deletion in
multiple stages leads to shorter blocking is generally not true.

I completely agree (that manual deletion is different to a cascading
delete). The only situation where the manual deletion would behave
differently if it was done in auto-commit mode, essentially having one
transaction per row.

.... or per table - depending how you do the deletions. IMHO that
approach (both row based as well as table based) is a recipe for
disaster because you cannot atomically undo the changes if you discover
that for whatever reason a row cannot be deleted while having committed
earlier deletes already.

Quote:

IMHO this would be a strange business case that someone is trying to
update rows that are being deleted by someone else.

If that business case would be nonexistent we probably would not have
RDBMS that support ACID properties of transactions. Point is, as this
*can* happen you need a system that supports transactional integrity in
some way. If not you have to deal with errors that occur from these
situations yourself (as is the case with some NoSQL databases).

True. But in 20 years I have rarely had applications where two different
users would _delete_ and update the same set of rows concurrently
(concurrent updates & selectare very common though)

Well, we all only see so much of the whole spectrum of applications out
there...

Quote:

And if that really happens, (b)locking is the right way to handle the
concurrent (and conflicting) access.

Absolutely! And adding to that: Oracle does it better than some other DB
vendors which only recently improved their concurrency models (SQL
Server comes to mind; IIRC versions up to 2000 did not handle this very
gracefully).

Yes. SQL Server 2000 really had a horrible concurrency when updates and
selects where happening at the same time (btw. DB2 wasn't that better as
well, even though IBM claims that the concurrency model in DB2 is
superior to the one in Oracle)

:-)

Quote:

PostgreSQL behaves the same as Oracle (it doesn't even need the FK
indexing, although that will help improve performance)

Thanks for that update!

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

#12

On Aug 6, 3:29*pm, CJ <cjsmith... (AT) gmail (DOT) com> wrote:

Quote:

On Aug 5, 4:44*pm, metaperl <metap... (AT) gmail (DOT) com> wrote:

Hello, I'm using an Oracle database where there are no ON DELETE
CASCADE in the DDL. As a result, when you need to delete something,
you have to calculate all the child tables and delete everything
related there and then finally delete the parent rows.

As a result, I have started to think that ON DELETE CASCADE is a good
idea. Are there any drawbacks to using this when designing a database?

As with anything, there are drawbacks to using ON DELETE CASCADE,
especially if used exclusively. A DBA should carefully analyze the
data being dealt with with and consider whether ON DELETE CASCADE or
ON DELETE SET NULL might be preferable. In some cases the data might
even dictate NOT using an ON DELETE clause.

Here's a (very) brief summary:
- Omitting the ON DELETE clause effectively locks out accidental
deletions. Oracle will not allow deletion of parent rows that are
currently being referenced in the child table. For example, a specific
part in the PARTS table cannot be deleted if it's on any INVOICES.

- ON DELETE CASCADE will delete any ROWS in the child table that
reference the deleted row in the parent table. Using the same example,
I would change the example to:

deleting a row from the INVOICE table will also delete all child rows
in the LINE_ITEMS table (IOW removes the whole invoice).
[]

Quote:

- ON DELETE SET NULL will change the foreign key value to NULL in any
parent rows. Again with the parts/invoices... All invoices with that
part will get 'blank' references. Note: this can be followed up with
an UPDATE statement that resets all the NULL values to a different key
value.

An example here would be deleting a part which actually is an
assembly. Deleting the assembly should be allowed to proceed, but you
want and need to keep the individual parts. Those parts would have
their "ASSEMBLY_PART_NO set to NULL when the Assembly is no longer
needed in the system.

Quote:

Again, the nature of the data will dictate which is best.

Exactly!

Quote:

Christopher J Smith
President
BeWise Consulting Group

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

CASCADE for delete and update

comp.databases comp.databases

Re: CASCADE for delete and update - 08-14-2010 , 07:57 AM

Re: CASCADE for delete and update - 08-15-2010 , 11:04 AM