چگونه در UPDATEها و DELETEها از قابلیت CASCADE استفاده کنیم؟؟

فرض کنید دو جدول به نام های dbo.Albums و dbo.Tracks داریم ، که به ترتیب حاوی لیست آلبوم ها و آهنگ هاست. همانطور که خودتان هم قطعا حدس زده اید dbo.Tracks دارای یک Foreign Key Constraint با ستونی مثلا به نام AlbumId به جدول dbo.Albums می باشد. حال اگر یک AlbumId در جدول Adbo.Albums آپدیت شود این آپدیت می بایستی روی تمامی آهنگ های جدول dbo.Tracks که دارای آن AlbumId بودند اعمال شود. این کار را می توانیم با قابلیتی به نام CASCADE به صورت خودکار انجام دهیم.

این حالت باعث می شود تا ستون مربوطه پس از UPDATE/DELETE شدن مرجع آن به NULL تغییر مقدار دهد.

این حالت باعث می شود تا پس از UPDATE شدن مقدار مرجع ، مقدار ستون مربوطه نیز آپدیت شده و پس از حذف شدن نیز سطر مربوطه حذف خواهد شد.

پس از اعمال UPDATE/DELETE در مقدار مرجع مقادیر مربوطه به مقدار Default آن ستون تغییر مقدار خواهند داد.

این حالت، حالت پیش فرض می باشد ، یعنی با زدن UPDATE/DELETE اتفاقی نمی افتد و error خواهید گرفت.

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'),
(۲, 'The End Of The Line', 1, '00:07:52'),
(۳, 'The Day That Never Comes', 1, '00:07:56'),
(۴, 'Battery', 4, '00:05:12')

در مثال بالا AlbumId در جدول dbo.Tracks به AlbumId در جدول dbo.Albums اشاره می کند. دو حالت CASCADE تعریف شده است.

وقتی سطری از جدول dbo.Albums پاک می شود تمامی سطرهایی که آن Id را در جدول dbo.Tracks داشتند دارای Id NULL  خواهند شد.

وقتی یک AlbumId در جدول dbo.Albums آپدیت می شود تمام سطرهایی که در جدول dbo.Tracks آن Id را داشتند Idشان به Id جدید آپدیت خواهد شد.

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

در مثال بالا دیدیم که AlbumId در جدول dbo.Tracks نیز به مقدار ۲ آپدیت شد ، مقدار قبلی ۴ بود.

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

در پایان برای اینکه ببینید کدام یک از Foreign Keyهای شما از قابلیت CASCADE استفاده می کنند می توانید از 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