کلید خارجی در SQL Server
در اینجا شما یاد خواهید گرفت که یک کلید خارجی چیست و چگونه با استفاده از یک کلید خارجی در پایگاه داده SQL Server بین دو جدول ارتباط برقرار کنید.
کلید خارجی چیست؟
کلید خارجی یا Foreign Key کلیدی است که برای اتصال دو جدول مورد استفاده قرار می گیرد که به آن کلید ارجاعی یا Referencing Key نیز گفته می شود. یک کلید خارجی در SQL ستونی است که ترکیبی از ستون هایی محسوب می شود که مقادیر آن ها با کلید اصلی (primary key) جدول دیگری مطابقت دارد. در رابطه بین دو جدول، کلید اصلی در یکی از جدول ها با یک کلید خارجی در جدول دوم مطابقت دارد. اگر یک جدول دارای یک کلید اصلی تعریف شده در هر فیلدی است، نمی تواند دو رکورد داشته باشد که دارای مقادیر مشابهی در فیلدها باشند. یک کلید خارجی در SQL محدودیتی است که برای اعمال یکپارچگی روی داده ها استفاده می شود. کلید خارجی توسط یک ستون (یا یک مجموعه از ستون ها) در جدولی ساخته شده است که به آن جدول والد یا Parent Table گفته می شود.
به بیان دیگر، کلید خارجی در SQL ستون یا گروهی از ستون ها در جدول پایگاه داده رابطه ای است که پیوندی را بین داده ها در دو جدول برقرار می کند. کلید خارجی در SQL مانند یک ارجاع متقابل بین جدول ها عمل می کند؛ زیرا به کلید اصلی جدول دیگری ارجاع دارد، در نتیجه پیوندی بین آن جدول ها برقرار می شود. اکثریت جدول ها در سیستم پایگاه داده رابطه ای به مفهوم کلید خارجی پایبند هستند. در پایگاه داده های پیچیده و انبار داده (Data Warehouse)، داده در یک دامنه باید به جدول های چندگانه اضافه شود، بدین ترتیب رابطه بین آن ها را حفظ می کند. مفهوم کلید خارجی در SQL و روش پیاده سازی آن از Primary Key کمی دشوارتر است.
همانطور که گفته شد کلید خارجی ارتباط بین دو جدول را برقرار می کند و یکپارچگی ارجاعی (referential integrity) را در SQL Server اعمال می کند. به عنوان مثال، جدول Employee زیر دارای یک ستون کلید خارجی DepartmentID است که به ستون کلید اصلی جدول Department مرتبط است.
نکات زیر را در رابطه با یک Foreign Key مورد توجه قرار دهید:
1- یک ستون کلید خارجی را می توان به یکprimary key یا یک ستون کلید منحصر به فرد از همان جدول یا جدول دیگر پیوند داد.
2- جدولی که دارای محدودیت کلید خارجی است، جدول فرزند و جدولی که توسط کلید خارجی ارجاع داده می شود، جدول والد نامیده می شود. به عنوان مثال. Employee یک فرزند و Department یک والد است.
3- مقداری غیر از NULL در ستون با محدودیت کلید خارجی وارد می شود که این مقدار باید قبلاً در ستون مرجع جدول والد وجود داشته باشد. در غیر این صورت یک خطای نقض کلید خارجی (foreign key violation error) دریافت خواهید کرد.
4- محدودیت های کلید خارجی می توانند به جداول در پایگاه داده یکسان در همان سرور ارجاع دهند.
5- محدودیت های کلید خارجی را می توان برای ارجاع به ستون دیگری در همان جدول تعریف کرد. این به عنوان خود ارجاع یا self-reference نامیده می شود.
6- یک محدودیت کلید خارجی در یک ستون واحد (محدودیت سطح ستون) می تواند تنها به یک ستون در جدول والد ارجاع دهد و باید همان نوع داده ستون ارجاع شده را داشته باشد.
7- یک محدودیت کلید خارجی تعریف شده در سطح جدول (بر روی ترکیبی از ستون ها) باید همان تعداد ستون های مرجع را داشته باشد مشابه با تعداد ستون های تعریف شده در لیست محدودیت ها. نوع داده هر ستون در محدودیت باید با ستون متناظر در لیست ستون یکسان باشد.
8- هیچ محدودیتی در تعداد محدودیت های کلید خارجی که یک جدول می تواند داشته باشد که به جداول دیگر ریفرنس دهد، وجود ندارد. با این حال، تعداد محدودیت های کلید خارجی به وسیله پیکربندی سخت افزار و طراحی پایگاه داده محدود شده است.
9- محدودیت های کلید خارجی در جداول موقت (temporary tables) اعمال نمی شود.
ایجاد محدودیت کلید خارجی در SQL Server
محدودیت های کلید خارجی را می توان به دو روش در SQL Server ایجاد کرد:
1- با استفاده از T-SQL
2- با استفاده از SQL Server Management Studio
ایجاد یک کلید خارجی با استفاده از T-SQL
یک کلید خارجی را می توان در اسکریپت T-SQL ایجاد جدول پیکربندی کرد. عبارت CONSTRAINT REFERENCES را در انتهای تعریف تمام ستون ها اضافه کنید.
در سینتکس بالا، <foreignkey_name> نام یک کلید خارجی است که باید در قالب FK_TableName_ReferenceTableName باشد تا به راحتی تشخیص داده شود. این به شما ایده ای در مورد جدول مرجع می دهد. <reference_tablename> نام جدولی است که در آن ستون ارجاع شده به عنوان کلید اصلی یا کلید منحصر به فرد (primary key or unique key) تعریف می شود.
اسکریپت T-SQL زیر یک جدول Employee جدید ایجاد می کند و یک محدودیت کلید خارجی FK_Employee_Department را روی ستون DepartmentID ایجاد می کند که به کلید اصلی DepartmentID جدول Department ارجاع می دهد.
ON DELETE CASCADE : وقتی یک کلید خارجی با استفاده از گزینه delete cascade ایجاد می کنیم، هر زمان که ردیف ارجاع شده در جدول والد با کلید اصلی حذف شود، ستون های مرجع در جدول فرزند حذف می شوند.
ON UPDATE CASCADE : هنگامی که یک کلید خارجی با گزینه update cascade ایجاد می شود، هر زمان که ردیف ارجاع شده در جدول والد با کلید اصلی به روزرسانی شود، ردیف های ارجاع دهنده در جدول فرزند به روزرسانی می شوند.
ایجاد کلید خارجی در یک جدول موجود
از عبارت ALTET TABLE ADD CONSTRAINT برای ایجاد یک کلید خارجی در جدول موجود استفاده کنید.
کوئری زیر یک محدودیت کلید خارجی جدید FK_Employee_Department روی ستون DepartmentID اضافه می کند.
ایجاد کلید خارجی با استفاده از SSMS
در اینجا، ما ستون DepartmentID را به عنوان یک کلید خارجی در جدول Employee پیکربندی می کنیم که به ستون DepartmentID PK جدول Department با استفاده از SQL Server Management Studio اشاره می کند.
SSMS را باز کنید و سپس پایگاه داده HR را باز دهید. مطابق شکل زیر بر روی جدول Employee کلیک راست کرده و روی گزینه Design کلیک کنید.
با این کار جدول Employee در حالت طراحی باز می شود.
اکنون در هر نقطه از table designer کلیک راست کرده و مانند شکل زیر گزینه ... Relationships را انتخاب کنید.
با این کار کادر محاوره ای روابط کلید خارجی (Foreign Key Relationships)، مطابق شکل زیر باز می شود.
حالا مانند تصویر زیر، روی دکمه Add کلیک کنید تا یک کلید خارجی جدید پیکربندی شود.
حال برای پیکربندی رابطه کلید اصلی و کلید خارجی، روی دکمه [...] Tables and Column Specification کلیک کنید. با این کار کادر محاوره ای جدول ها و ستون ها باز می شود که در آن می توانید رابطه کلید اصلی و کلید خارجی را انتخاب کنید.
در اینجا، ما ستون DepartmentID در جدول Employee را به عنوان یک کلید خارجی انتخاب می کنیم که به ستون کلید اصلی DepartmentID جدول Department اشاره می کند. بنابراین، مانند تصویر زیر، جدول و کلید اصلی را در سمت چپ و جدول و ستون کلید خارجی را در سمت راست انتخاب کنید.
در آنچه که در زیر می بینید کلید خارجی DepartmentID در جدول Employee تعریف می شود.
روی OK کلیک کنید تا رابطه ایجاد شود و روی Close کلیک کنید تا کادر محاوره ای بسته شود.
اکنون، تغییرات خود را ذخیره کنید. با ست کردن یک کلید خارجی بر روی ستون DepartmentID در جدول Employee، مانند شکل زیر، یک رابطه یک به چند (one-to-many) بین جدول Employee و Department ایجاد می شود.
