Sql And Me

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