سرفصل مطالب
-
مقدمه
-
معرفی قید کلید خارجی (Introducing Foreign Key Constraint)
-
تعریف و پیاده سازی کلید خارجی (Implementing and Declaring Foreign Key Constraint)
-
ارتباطات بین جداول (relationships / cardinality)
مقدمه
طراحی بانک (database design) را می توانیم به دو قسمت کلی تقسیم کنیم. طراحی منطقی (logical) و طراحی و پیاده سازی فیزیکی (physical). در این مقاله ی مرتبط با طراحی بانک قصد ندارم بحثی راجب طراحی منطقی (شامل سرفصل هایی چون نرمالسازی، data modeling و ...) انجام دهم. ولی به پیاده سازی آن توسط دستورات DDL بطور نسبتا مفصلی پرداخته خواهد شد.
قید کلید خارجی (Foreign Key Constraint)
همانطوری که از عنوان پیداست، کلید خارجی یک نوع قید (constraint) است که اجازه ی درج هر مقداری را در ستون (یا ستونها) مورد نظر نمی دهد. قید کلید خارجی برای پیاده سازی Referential Integrity استفاده می شود که جزئی از Data Integrity هستند.
داده های موجودیت های مختلف در جداول مختلفی ذخیره می شوند، بنابراین برای دستیابی به اطلاعات نیاز هست که بین جداول پیمایش کنیم، مدل رابطه ای (Relational Model) برای این منظور یک مکانیزمی را در اختیار گذاشته است که کلید خارجی (Foreign Key) نامیده می شود.
یک Foreign Key دارای خصیصه ها و ویژگی های زیر است:
-
این یک ستون (یا چند ستون) از یک جدول است که به مقدار موجود در جدول دیگر اشاره و ارجاع می کند.
-
جدولی که دارای کلید خارجی است، جدول فرزند (child) یا referencing گفته می شود. و به جدول دیگر جدول والد (parent) یا referenced گفته می شود.
-
یک کلید خارجی یک ارتباط مستقیم با کلید اصلی یا کلید کاندید (unique) جدول parent را تشکیل می دهد. بنابراین مقادیر FK محدود به مقادیر درج شده در parent-key می باشد. این قید کلید خارجی، Referential Integrity نامیده می شود. این قیود را با کمک دستورات تعریفی (Declarative) ایجاد می کنیم. که به اصطلاح به آنها Declarative Referential Integrity یا DRI به شکل خلاصه گفته می شود. (برای جامعیت داده ها بویژه Referential Integrity نرم افزار SQL Server دو مکانیزم در اختیار ما قرار داده است یکی تعریفی و دیگری رویه ای، رویه ای مثل trigger)
-
data type کلید خارجی بایستی با data type کلید parent یکسان باشد. یا اینکه data type کلید خارجی قابل تبدیل (convertible) به نوع داده parent-key باشد.
-
برخلاف Primary Key ها که نمی توانند Nullable باشند، کلیدهای خارجی می توانند Null بپذیرند. البته بدیهی است که باید ستون کلید خارجی Allow Null باشد.
-
Foreign Key ممکن است Primary Key نیز باشد. بطور مثال در یک ارتباط یک به یک، در یکی از جداول کلید اصلی، کلید خارجی نیز هست.
-
در بعضی از سناریو ها ممکن است که parent-key یک foreign key در همان جدول باشد. یعنی کلید خارجی به کلید اصلی همان جدول ارجاع می کند. که به اصطلاح به آن self-referncing گفته میشود. یک طراحی معروفی که می توانیم مثال بزنیم، جدول کارمندان است.
-
هدف از ایجاد کلید خارجی جلوگیری از درج رکورد های یتیم (Orphan) است. یعنی اگر قیدی وجود نداشته باشد می توانیم در جدول Child مقادیری را درج کنیم که در جدول Parent هنوز درج نشده اند. مثلا برای فردی انتخاب واحد کنیم که جزء دانشجویان نبوده است. این موضوع باعث زیر سوال رفتن جامعیت داده ها شده و در تمام موارد مشکل ساز خواهد بود.
به دو تصویر زیر دقت کنید. در تصاویر زیر parent table و child table و primary key و foreign key کاملا مشخص است.
(این تصویر همراه با برخی از مطالب دیگر را از کتابی به نام Visual Quikstart Quide SQL Third Edition By Chris Fehily انتخاب کرده ام. اگر علاقه مند هستین که SQL را سریع، کامل، زیبا و مفهومی یاد بگیرید و آموخته هایتان را در نرم افزارهای تجاری مدیریت بانک اطلاعاتی چون SQL Server ، Oracle، Access و IBM DB2 و نرم افراهای open source ای چون MySQL بکار بگیرین این کتاب را حتما بخوانید!)
تعریف کلید خارجی
برای تعریف و ایجاد کلید خارجی دو حالت وجود دارد. یکی اینکه در زمان طراحی جدول این کلید تعریف شود و دیگری بعد از تعریف جدول بخواهیم کلید خارجی برای آن ایجاد کنیم. توجه داشته باشید که از ابزارهای کمکی طراحی بانک استفاده نخواهم کرد. تنها با تکیه بر دستورات DDL اینکار صورت خواهد گرفت.
حالت اول: فرض کنید یک جدول به نام Suppliers داریم که لیست تولید کنندگان در آن نگهداری می شوند. جدول دیگری به نام Parts محصولات را دربرگرفته است و جدول سومی که مشخص می کند کدام بخش توسط کدام تولید کننده آماده شده است.
به دستورات تعریف جداول زیر دقت کنید. کلید خارجی یک نوع قید است، قیود را می توانیم به دو شکل تعریف کنیم "قید ستونی یا Column Constraint" و "قید جدولی یا Table Constraint". در بعضی از مواقع ما ناچاریم که از قید نوع دوم یعنی قید جدولی استفاده کنیم. در جدول اتصال (جدول سوم) ترکیب دو ستون sno و pno کلید اصلی را تشکیل می دهند در اینجا بایستی از Table Constraint استفاده شود.
CREATE TABLE Suppliers
(sno VARCHAR(10) NOT NULL PRIMARY KEY,
sname VARCHAR(50) NOT NULL);
CREATE TABLE Parts
(pno VARCHAR(10) NOT NULL PRIMARY KEY,
pname VARCHAR(50) NOT NULL);
--===== Column Constraint =======
CREATE TABLE supparts
(sno VARCHAR(10) NOT NULL
--Referential Integrity
CONSTRAINT fk_sno
REFERENCES suppliers (sno),
pno VARCHAR(10) NOT NULL
--Referential Integrity
CONSTRAINT fk_pno
REFERENCES parts (pno),
PRIMARY KEY (sno, pno));
--===== Table Constraint =======
CREATE TABLE supparts
(sno VARCHAR(10) NOT NULL,
pno VARCHAR(10) NOT NULL,
--Referential Integrity
CONSTRAINT fk_sno
FOREIGN KEY (sno)
REFERENCES suppliers (sno),
CONSTRAINT fk_pno
FOREIGN KEY (pno)
REFERENCES parts (pno),
PRIMARY KEY (sno, pno));
قسمتی که مربوط به نام قید می باشد یعنی CONSTRAINT const_name اختیاری هست. به این معنا که می توانید از آن صرف نظر کرده و SQL Server خود وظیفه ی نام گذاری ها را به عهده بگیرد. همانطوری که مشاهده می کنید در قید نوع ستونی ما نیاز به ذکر ماده ی FOREIGN KEY نداریم ولی در نوع جدولی نوشتن این ماده الزامی است.
حالت دوم: در این حالت ما بعد از ایجاد جدول با کمک دستور ALTER TABLE کلید های خارجی را برای ستون هایی که از قبل ایجاد شده اند تعریف می کنیم. نکته ای که وجود دارد این است که اگر داده هایی در جدول Child درج شده باشند که یتیم باشند (یعنی در جدول Parent وجود نداشته باشند) با خطای Conflict مواجه خواهید شد. (البته می توانید از امکان WITH NOCHECK استفاده کنید)
CREATE TABLE supparts
(sno VARCHAR(10) NOT NULL,
pno VARCHAR(10) NOT NULL,
PRIMARY KEY (sno, pno)) ;
ALTER TABLE supparts
ADD CONSTRAINT fk_sno
FOREIGN KEY (sno)
REFERENCES suppliers (sno) ;
ALTER TABLE supparts
ADD CONSTRAINT fk_pno
FOREIGN KEY (pno)
REFERENCES parts (pno) ;
دیاگرامی که بعد از تعریف جداول و قیود بدست می آید به شکل زیر است:
ارتباطات بین جداول (relationships)
دو جدول به چند طریق می توانند به همدیگر Link شوند.
-
one-to-one
-
one-to-many
-
many-to-many
یک به یک: در این نوع از ارتباط هر سطر از جدول A تنها می تواند با یک سطر از جدول B جور (Match) شود. و بالعکس.
از این نوع ارتباط عموما برای موارد زیر استفاده می شود.
-
تفکیک کردن اطلاعات سری برای دلایل امنیتی
-
سرعت بخشیدن به Query با تجزیه کردن جدول
-
جلوگیری کردن از درج مقادیر NULL در جدول
در مورد سوم زمانی که نمونه های (instances) محدودی از یک موجودیت (entity) دارای مقدار برای برخی خصیصه ها (attributes) هستند مورد استفاده قرار می گیرد. بطور مثال فرض کنید در حال طراحی بانک برای یک مدرسه هستیم. در این مدرسه یک تیم فوتبال وجود دارد که تنها تعداد محدودی از دانش آموزان عضو آن هستند. برای هر فوتبالیست ما مشخصاتی چون "شماره پیراهن"، "پست"، "تعداد کل بازیها" و ... را نیاز داریم. حالا اگر ما این خصیصه های مربوط به فوتبالیست ها را داخل جدول "دانش آموزان" که مربوط به مشخصات کلی یک دانش آموز مدرسه ای است ذخیره کنیم قطعا این ستون ها برای چند بازی کن مقدار خواهد داشت و برای بقیه دانش آموزان مقدار NULL. در این حالت ما جدول را به دو جدول می شکنیم و یک ارتباط یک به یک بین این دو بوجود می آوریم.
به این تصویر دقت کنید که یک ارتباط یک به یک را از طریق یک نمودار و جدول شرح می دهد:
در این نوع، ارتباط بین primary-key دو جدول ایجاد شده است. بطور مثال کلید اصلی جدول royalties یک کلید خارجی نیز هست که به کلید اولیه جدول titles ارجاع داده شده است.
یک به چند: در این رابطه یک سطر در جدول A می تواند با چندین (صفر یا بیشتر) سطر از جدول B جفت و جور (Match) شود (و هر سطر از جدول B تنها با یک سطر از جدول A جور می شود). مثلا جدول Customers با جدول Orders یک ارتباط یک به چند دارند. به این معنا که یک مشتری می تواند صفر، یک یا هر چند تا سفارش در جدول سفارشات داشته باشد. یا اینکه هر انتشاراتی می تواند چندین کتاب انتشار دهد ولی هر کتاب توسط یک انتشارات به انتشار می رسد. برای مشاهده ی چنین ارتباطی به اولین نمودار و جدول در این مقاله رجوع کنید.
چند به چند: در این نوع ارتباط هر سطر از جدول A می تواند چندین سطر Match شده در جدول B داشته باشد و هر سطر از جدول B نیز می تواند با هر چند سطر از جدول A جور شود. مثلا در آمار پرواز، یک هواپیما توسط چندین خلبان به پرواز درامده و هر خلبان می تواند چندین هواپیما را به پرواز در آورد. در نتیجه یک سطر از جدول "هواپیما" می تواند با چندین سطر از جدول "خلبان" جفت و جور شود و با العکس. برای پیاده سازی این نوع ارتباط نیاز به یک جدول واسط (Junction Table) داریم. که در این جدول واسط دو کلید خارجی که به دو جدول اصلی ارجاع داده شده اند وجود دارد و ترکیب این دو ستون کلید اصلی را تشکیل می دهند (البته نه در تمام سناریوها).
مطالب دیگری که در مورد کلید خارجی باقیمانده اند را در زیر لیست کرده ام که شما می توانید با کمک Books Online با آنها آشنا شده و مشکلات مربوطه را رفع کنید:
Forcing a foreign key constraint by using WITH NOCHECK
Cascading Referential Integrity Constraint
Self Referencing
Multiple Cascading Actions
Multiple Cascading Paths