Home > SQLServer > SQL Server – How to cascade UPDATEs and DELETEs to related tables

SQL Server – How to cascade UPDATEs and DELETEs to related tables

In most cases we use stored procedures to update or delete rows from related tables. But, you can also cascade the actions using foreign key constraints. This is generally not used due to the fact that it is transparent, and leads to *mysterious* results. Let’s see how it works.

Consider two different tables dbo.Albums and dbo.Tracks, they contains a list of Albums and a list of Tracks in each Album respectively. dbo.Tracks contains a foreign key constraint to AlbumID in dbo.Albums. Now, if we update an AlbumID in dbo.Albums, this also need to be updated in dbo.Tracks for all Tracks belonging to AlbumID.

 

Cascading can be defined for UPDATE and DELETE. There are four different options available:

1. SET NULL:

This action specifies that the column will be set to NULL when the referenced column is updated/deleted.

2. CASCADE:

CASCADE specifies that the column will be updated when the referenced column is updated, and rows will be deleted when the referenced rows are deleted.

3. SET DEFAULT:

Column will be set to DEFAULT value when UPDATE/DELETE is performed on referenced rows.

4. NO ACTION:

This is the default behavior. If a DELETE/UPDATE is executed on referenced rows, the operation is denied. An error is raised.

 

Let’s look at this with an example,

CREATE TABLE dbo.Albums

(

      AlbumID     INT   PRIMARY KEY,

      Name        VARCHAR(50)

)

 

CREATE TABLE dbo.Tracks

(

      TrackID     INT   PRIMARY KEY,

      Title       VARCHAR(50),

      AlbumID     INT   REFERENCES Albums(AlbumID)

                        ON DELETE SET NULL

                        ON UPDATE CASCADE,

      Duration    TIME(0)

)

INSERT      INTO dbo.Albums (AlbumID, Name)

VALUES      (1,'Death Magnetic'), (4,'Master Of Puppets')

           

INSERT      INTO dbo.Tracks (TrackID, Title, AlbumID, Duration)

VALUES      (1, 'That Was Just Your Life' , 1, '00:07:08'),

            (2, 'The End Of The Line', 1, '00:07:52'),

            (3, 'The Day That Never Comes', 1, '00:07:56'),

            (4, 'Battery', 4, '00:05:12')

In the above example, AlbumID in dbo.Tracks references AlbumID in dbo.Albums. There are two cascading actions specified here.

ON DELETE SET NULL = When a row is deleted from dbo.Albums, AlbumID will be set to NULL for all matching rows in dbo.Tracks.

ON UPDATE CASCADE = When AlbumID is updated in dbo.Albums, all matching rows in dbo.Tracks will also have the updated AlbumID.

Now, let’s try and update an AlbumID in dbo.Albums:

UPDATE      dbo.Albums

SET         AlbumID = 2

WHERE       AlbumID = 4

 

SELECT      A.AlbumID, A.Name,

            T.TrackID, T.Title, T.AlbumID, T.Duration

FROM  dbo.Albums A

RIGHT JOIN dbo.Tracks T ON A.AlbumID = T.AlbumID

Result Set:

(1 row(s) affected)

 

AlbumID   Name               TrackID  Title                       AlbumID  Duration

1         Death Magnetic     1        That Was Just Your Life     1        00:07:08

1         Death Magnetic     2        The End Of The Line         1        00:07:52

1         Death Magnetic     3        The Day That Never Comes    1        00:07:56

2         Master Of Puppets  4        Battery                     2        00:05:12

 

(4 row(s) affected)

From the above output we can see that AlbumID has also been set to 2 in dbo.Tracks, where it was 4 earlier.

For delete also it works in the same way:

DELETE FROM dbo.Albums

WHERE AlbumID = 1

 

SELECT      A.AlbumID, A.Name,

            T.TrackID, T.Title, T.AlbumID, T.Duration

FROM        dbo.Albums A

RIGHT JOIN dbo.Tracks T ON A.AlbumID = T.AlbumID

Result Set:

 

(1 row(s) affected)

 

AlbumID   Name               TrackID  Title                       AlbumID  Duration

NULL      NULL               1        That Was Just Your Life     NULL     00:07:08

NULL      NULL               2        The End Of The Line         NULL     00:07:52

NULL      NULL               3        The Day That Never Comes    NULL     00:07:56

2         Master Of Puppets  4        Battery                     2        00:05:12

 

(4 row(s) affected)

You can check if any foreign key is defined in your database with cascading actions using sys.foreign_keys:

SELECT      name AS [Constraint Name],

            OBJECT_NAME(referenced_object_id) [Referenced Object],

            OBJECT_NAME(parent_object_id) [Parent Object],

            delete_referential_action_desc [ON DELETE],

            update_referential_action_desc [ON UPDATE]

FROM        sys.foreign_keys

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
Categories: SQLServer
  1. swati
    February 23, 2012 at 2:01 pm

    Nice Post !!

  2. Brajesh
    October 18, 2012 at 3:33 pm

    awesome post 🙂

  3. ARUN MENON
    January 24, 2013 at 8:53 pm

    THANK YOU VISHAL….

  4. Ramendra Kumar
    May 8, 2013 at 11:31 am

    good post

  5. Jason
    May 30, 2013 at 4:40 pm

    Hello,

    What do you mean by “leads to *mysterious* results”?

    I like the example, but where are the mysterious results described?

    Thank you,

    Jason

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: