Prev: SQL Server Ranking: Count Consecutive Values / Reset
Next: Collecting data...!
From: shapper on 13 Jul 2010 08:47
Hello,

When should I cascade on update or on delete?

On delete I am cascading all relationships not including the ones with
Lookup tables.

But should I use it on Update?

Thanks,
Miguel
From: Paul Shapiro on 13 Jul 2010 10:47
"shapper" <mdmoura(a)gmail.com> wrote in message
news:5049af1f-507d-4c73-a10f-a70013b7e7e2(a)s9g2000yqd.googlegroups.com...
> Hello,
>
> When should I cascade on update or on delete?
>
> On delete I am cascading all relationships not including the ones with
> Lookup tables.
>
> But should I use it on Update?

Cascading should be done whenever you want to make it easier for the user to
take the related action, and the automatic cascade will ALWAYS be the
correct option. If the primary key cannot ever change, there's no need to
cascade updates. If the PK can change and you are sure that updating all
child foreign keys to match the new PK value will always be correct, then I
would cascade the updates.

From: --CELKO-- on 13 Jul 2010 11:50
>> When should I cascade on update or on delete? <<

Whenever it makes sense with your business rules. Do not leave DRI
actions off when you can use them correctly. They are safer, faster
and more portable than trying to do data integrity in procedural code.
From: Eric Isaacs on 13 Jul 2010 18:18
> Whenever it makes sense with your business rules. Do not leave DRI
> actions off when you can use them correctly. They are safer, faster
> and more portable than trying to do data integrity in procedural code.

I agree with Joe Celko. I would also add that they are cheaper and
easier to maintain as well.

Cascade deletes especially are helpful if used carefully. In many
cases, you wouldn't want to cascade delete a look-up table, because
you don't want to delete a row of important user data if the look-up
value is removed. You want the users to fix those rows before they
can delete the look-up value. You would rather the value be cleared
(using DRI) or changed to another value before the look-up rows are
removed. It's a business decision as to when to automatically delete
the rows.

Cascade updates are very helpful if you're using natural keys instead
of surrogate keys (that are not exposed to the end users.) Cascade
updates aren't as useful if you're using INT IDENTITY columns as your
primary keys.

This is a very simplified example and I wouldn't normalize a database
this way, but lets say you have a Classroom table that has say a
RoomNumber, a StudentID and an InstructorID in it, you may want to
allow cascade deletes from the Student table to the Classroom table,
but you probably don't want to allow cascade deletes from the
Instructor table. You would want the DRI to prevent the instructors
from being deleted until the classrooms were assigned new instructors
to take their place. This is why it's a business decision as to
whether you implement it on a case by case basis.

-Eric Isaacs



From: Erland Sommarskog on 13 Jul 2010 18:25
shapper (mdmoura(a)gmail.com) writes:
> When should I cascade on update or on delete?
>
> On delete I am cascading all relationships not including the ones with
> Lookup tables.
>
> But should I use it on Update?

When it comes to DELETE, use NO ACTION when you don't knwo.

When it comes to UPDATE, well, if someone wants to change the id
for the "shopkeepers" customer category from 13 to 234, no big deal.
....unless there is a table where this value appears in millions and
millions of rows.

Personally, I am of the position that PKs should be immutable, which
makes the question moot.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2 3
Prev: SQL Server Ranking: Count Consecutive Values / Reset
Next: Collecting data...!