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

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

نوشته شده توسط: میلاد فیروزی
تاریخ انتشار: ۱۲ دی ۱۳۹۴
آخرین بروزرسانی: ۲۴ آبان ۱۴۰۲
زمان مطالعه: 12 دقیقه
۴.۱
(۷)

مقدمه

در اکثر مواقع برای حذف کردن و یا آپدیت کردن سطرها و سطرهای مرتبط در جدوال مربوطه از Stored Procedureها بهره می بریم ، ولی می توان این عملیات را توسط اعمال کردن CASCADE در Foreign Key Consraints انجام داد.

فکر می کنم درک کردن این موضوع با طرح یک مثال آسان تر باشد

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

قابلیت CASCADE برای UPDATE و DELETE تعریف می شود و ۴ حالت متفاوت نیز برای این قابلیت وجود دارد

SET NULL 

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

CASCADE 

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

SET Default 

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

No Action 

این حالت، حالت پیش فرض می باشد ، یعنی با زدن 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 تعریف شده است.

ON DELETE SET NULL

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

ON UPDATE CASCADE

وقتی یک AlbumId در جدول dbo.Albums آپدیت می شود تمام سطرهایی که در جدول dbo.Tracks آن Id را داشتند Idشان به Id جدید آپدیت خواهد شد.
حال بیایید یک سطر از جدول 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
(۱ row(s) affected)
AlbumID   Name               TrackID  Title                       AlbumID  Duration
۱         Death Magnetic     ۱        That Was Just Your Life     ۱        ۰۰:۰۷:۰۸
۱         Death Magnetic     ۲        The End Of The Line         ۱        ۰۰:۰۷:۵۲
۱         Death Magnetic     ۳        The Day That Never Comes    ۱        ۰۰:۰۷:۵۶
۲         Master Of Puppets  ۴        Battery                     ۲        ۰۰:۰۵:۱۲
(۴ row(s) affected)
در مثال بالا دیدیم که AlbumId در جدول dbo.Tracks نیز به مقدار ۲ آپدیت شد ، مقدار قبلی ۴ بود.

برای DELETE

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
(۱ row(s) affected)
AlbumID   Name               TrackID  Title                       AlbumID  Duration
NULL      NULL               ۱        That Was Just Your Life     NULL     ۰۰:۰۷:۰۸
NULL      NULL               ۲        The End Of The Line         NULL     ۰۰:۰۷:۵۲
NULL      NULL               ۳        The Day That Never Comes    NULL     ۰۰:۰۷:۵۶
۲         Master Of Puppets  ۴        Battery                     ۲        ۰۰:۰۵:۱۲
(۴ row(s) affected)
در پایان برای اینکه ببینید کدام یک از 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

 

 

برای بدست آوردن اطلاعات بیش‌تر در مورد دیگر دستورات SQL ، به مقاله زیر مراجعه کنید.
 
دستورهای SQL Server

چه رتبه ای می‌دهید؟

میانگین ۴.۱ / ۵. از مجموع ۷

اولین نفر باش

title sign
معرفی نویسنده
میلاد فیروزی
مقالات
8 مقاله توسط این نویسنده
محصولات
0 دوره توسط این نویسنده
میلاد فیروزی
پروفایل نویسنده
title sign
دیدگاه کاربران

    • چرا در اکسس ADO این اتفاق در C# نمی افته؟
      من تست میکنم نمیشه

    • بسم الله الرحمن الرحیم
      سلام علیکم
      عالی بود…

    • خیلی عالی بود ممنونم

    • سلام ببخشید یک سوال داشتم
      میشه اطلاعات دوتا جدول رو به صورت زیر آپدیت کرد
      UPDATE table_a a
      SET ( c1, c2, …)
      = (b.c1, b.c2, …)
      FROM table_b b
      WHERE a.id = b.id;
      خیلی ممنون

    • سلام ببخشید یک سوال داشتم
      میشه اطلاعات دوتا جدول رو به صورت زیر آپدیت کرد
      UPDATE table_a a
      SET ( c1, c2, …)
      = (b.c1, b.c2, …)
      FROM table_b b
      WHERE a.id = b.id;
      خیلی ممنون

    •    با سلام و خسته نباشید خدمت آقای میلاد فیروزی

      مقاله بسیار جالبی بود خیلی برای من مفید بود

      با تشکر از شما

    •     سلام تاثیر FK ها روی کارایی و سرعت در INSERT و UPDATE و DELETE در حالتیکه نیازی به اعمال تغییرات روی جداول وابستشون نباشه چجوریه؟ برای مثال فیلدی از جدول UPDATE میشه که ربطی به جدول وابسته نداره تو این حالت حدود دو سال پیش تست کرده بودم سرعت پایین میاد الان نمی دونم که هنوزم همون مکانیزم هست یا نه

    •     با سلام بسیار عالی بود

    •     با سلام و تشکر از مقاله شما

      سوالی که پیش میاد چه زمانی نیاز میشه مقدار یک کلید اصلی عوض بشود ؟ مخصوصن زمانی که از یک id استفاده میکنیم نه مثلا کد ملی
      و سوال بعد اینکه وقتی یک آلبوم حذف میشود بهتر نیست آهنگ های آن نیز حذف شود؟ چون دیگه مثل قبل دسترسی به آنها نداریم و مشخص نیستند این آهنگها برای کدوم آلبوم بوده اند؟
      با تشکر از شما
      •    سلام
        در رابطه با این قضیه که چه زمانی پیش میاد که اطلاعات جدول Master حذف بشه ولی اطلاعات جدول Details حذف نشه من دریک پروژه حسابداری برام به این صورت بیان شد که در حسابداری نوین و کنونی (نه ۵۰ سال پیش!) یک تفضیل (Details) میتونه معین (Master) داشته باشه یا نداشته باشه.

      •     خوب قطعا حق با شماست

        چون تو اکثر مواقع Foreign Key به یک Identity وصله 
        ولی خوب ممکنه این اتفاق نیوفته و خوب در همچین مواقعی این قابلیت می تونه کمک کنه
        و با این قابلیت تصمیم می گیریم که اگه آلبوم حذف شد سر آهنگ ها چه بلایی میاد
        حذف می شود آی دیشون Null میشه و یا آیدیشون به Default Value تغییر می کنه
هر روز یک ایمیل، هر روز یک درس
آموزش SQL Server بصورت رایگان
همین حالا فرم زیر را تکمیل کنید
دانلود رایگان جلسه اول
نیک آموز علاوه بر آموزش، پروژه‌های بزرگ در حوزه هوش تجاری و دیتا انجام می‌دهد.
close-link