Many a times you need to delete the record but at the same time you need to make sure all the related or referenced records are also deleted.
This is because you can not delete a record if it is referenced in another table.
In this case you can either delete the referenced record or you can set the “Cascade Delete On”.
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.
CREATE TABLE [dbo].[Tracks](
[TrackID] [int] NOT NULL PRIMARY KEY,
[Title] [varchar](50) NULL,
[AlbumID] [int] NULL,
[Duration] [time](0) NULL)
CREATE TABLE [dbo].[Albums](
[AlbumID] [int] NOT NULL PRIMARY KEY,
[Name] [varchar](50) NULL)
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′)
//Add Foreign key Using Query
ALTER TABLE [dbo].[Tracks] WITH CHECK add FOREIGN KEY([AlbumID])
REFERENCES [dbo].[Albums] ([AlbumID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
//Update Query for cascade update
UPDATE dbo.Albums
SET AlbumID = 4
WHERE AlbumID = 2
//Delete Query for cascade delete
DELETE FROM dbo.Albums
WHERE AlbumID = 1
