انواع constraint ها: FOREIGN KEY و PRIMARY KEY

با سلام و عرض ادب خدمت شما همراهان گرامی روکسو، در قسمت قبلی از این سری آموزشی در رابطه با قوانینی صحبت کردیم که بر جدول ها حاکم هستند. دسته ای از این قوانین تعیین کننده ی Datatype ها یا نوع داده هایی بودند که روی هر فیلد جدول اعمال می شود و رفتار آن را مشخص می کند. دسته ی دیگر وظیفه ی مشخص کردن Primary Key ها و ... را داشتند و الی آخر. ما به این قوانین constraint میگوییم که در لغت به معنی «قید» یا «محدودیت» است.

همچنین گفتیم که در دو جا می توان از constraint ها استفاده کرد:

• هنگام ساختن جدول جدید با استفاده از دستور CREATE TABLE
• هنگام ویرایش جدول قبلی با استفاده از دستور ALTER TABLE

ساختار کلی constraint ها را در کد زیر مشاهده می کنید:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

همچنین گفتیم که constraint ها در دو سطح فعالیت می کنند:

• در سطح جدول (table level): قوانینی که در این سطح باشند روی کل جدول پیاده می شوند.
• در سطح ستون ها (column level): قوانینی که در این سطح باشند تنها روی ستون هایی پیاده سازی می شوند که ما مشخص کرده باشیم.

لیست constraint های زبان SQL را مشاهده می کنید:

• NOT NULL: به ستون ها اجازه نمی دهد که مقدار NULL داشته باشند.
• UNIQUE: می گوید تمام مقادیر یک ستون باید متفاوت باشند.
• PRIMARY KEY: ترکیبی از NULL و UNIQUE است و کار تشخیص ردیف های خاص را بر عهده دارد.
• FOREIGN KEY: کار تشخیص ردیف های خاص از یک جدول دیگر را بر عهده دارد.
• CHECK: برقرار بودن شرط یا محدودیت خاصی را بررسی می کند.
• DEFAULT: برای ستون ها مقادیر پیش فرضی تعیین می کند تا در صورت نبود هیچ مقدار خاصی، آن مقدار پیش فرض جایگزین شود.
• INDEX: برای دریافت و ایجاد داده با سرعت بسیار بیشتر مورد استفاده قرار می گیرد.

در قسمت قبل در مورد NOT NULL و همچنین UNIQUE صحبت کردیم و در این قسمت می خواهیم در رابطه با دیگر موارد بحث کنیم.

 PRIMARY KEY

PRIMARY KEY ها (به معنی «کلید اولیه») داده های یک جدول را به صورت خاص شناسایی می کنند. داده های PRIMARY KEY باید مقادیر UNIQUE داشته باشند و همچنین نمی توانند NULL باشند. در ضمن، هر جدول تنها می تواند یک PRIMARY KEY داشته باشد که خود می تواند از یک یا چند فیلد تشکیل شده باشد.

فرض کنید بخواهیم ستون ID را به عنوان PRIMARY KEY جدول تعریف کنیم. در این صورت در MySQL به این شکل عمل می کنیم:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

و در SQL Server و Oracle و MS Access به این شکل عمل می کنیم:

CREATE TABLE Persons (
    ID int NOT NULL PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

البته اگر می خواهید اجازه دهید تا PRIMARY KEY نام گذاری شود و همچنین روی چندین ستون اعمال شود، می توانید از ساختار زیر برای تمام پایگاه های داده استفاده کنید:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

سوال: مگر نگفتیم که هر جدول فقط یک PRIMARY KEY دارد؟ پس چطور در مثال بالا آن را روی چند ستون اعمال کردیم؟!

پاسخ: مثال بالا تنها داری یک PRIMARY KEY است و آن هم PK_Person می باشد اما مقدار یا محتوای PRIMARY KEY از دو ستون تشکیل شده است (ID و LastName).

استفاده از ALTER TABLE

اگر بخواهید از PRIMARY KEY در دستور ALTER TABLE استفاده کنید باید به شیوه ی دیگر عمل کنید. تصور کنید از قبل جدولی داریم و می خواهیم ستون ID آن را به عنوان PRIMARY KEY تعیین کنیم. در این صورت برای MySQL و SQL Server و Oracle و MS Access می نویسیم:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

و اگر بخواهیم PRIMARY KEY روی چندین ستون پیاده سازی شود می گوییم:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

هشدار: اگر می خواهید از دستور ALTER TABLE برای تعیین PRIMARY KEY استفاده کنید باید مطمئن شوید که ستون های مورد نظرتان، در هنگام ساختن جدول، از قبل طوری تنظیم شده باشند که مقادیر NULL نداشته باشند.

حذف PRIMARY KEY

همچنین برای حذف یک PRIMARY KEY می توانید از دستور زیر برای MySQL استفاده کنید:

ALTER TABLE Persons
DROP PRIMARY KEY;

و از این دستور برای SQL Server و Oracle و MS Access:

ALTER TABLE Persons
DROP CONSTRAINT PK_Person;

سوال: فایده و کاربرد PRIMARY KEY ها چیست؟

پاسخ: همانطور که گفتیم، PRIMARY KEY معمولا به صورت یک ستون خاص انتخاب می شوند. ما بعدا می توانیم از این ستون خاص برای شناسایی یک ردیف استفاده کنیم. به طور مثال اگر ستون ID را به عنوان PRIMARY KEY در نظر بگیریم می توانیم از طریق هر id ردیف مورد نظرش (یعنی یکی از کاربران خود) را پیدا کنیم. همچنین در محیط هایی مانند MySQL می توانیم PRIMARY KEY را به عنوان FOREIGN KEY یک جدول دیگر در نظر بگیریم و بین دو جدول رابطه ایجاد کنیم! آیا می دانید FOREIGN KEY چیست؟

FOREIGN KEY

FOREIGN KEY (به معنی «کلید خارجی») برای متصل کردن دو جدول به هم استفاده می شود. در واقع FOREIGN KEY ها یک فیلد یا مجموعه ای از فیلد های جدول هستند که به PRIMARY KEY های یک جدول دیگر اشاره می کنند! در این حالت جدولی که foreign key را داشته باشد «جدول فرزند» یا child table و جدول دیگر «جدول پدر» یا parent table نام خواهد داشت.

به دو جدول زیر نگاه کنید:

جدول Persons:

جدول Orders:

اگر دقت کنید متوجه می شوید که ستون PersonID در جدول Orders به ستونِ PersonID در جدول Persons اشاره می کند! بنابراین می توان گفت:

• ستون PersonID که در جدول Persons است، یک PRIMARY KEY برای جدول Persons محسوب می شود.
• ستون PersonID که در جدول Orders است، یک FOREIGN KEY برای جدول Orders محسوب می شود.

باید بدانید FOREIGN KEY از کارهایی که باعث نابود شدن ارتباط بین دو جدول می شوند جلوگیری می کند. همچنین از وارد شدن داده های نامعتبر به ستونِ FOREIGN KEY جلوگیری می کند چرا که FOREIGN KEY باید یکی از مقادیر جدولی را داشته باشد که در حال اشاره به آن است.

ساخت FOREIGN KEY

فرض کنید می خواهیم ستون PersonID را به عنوان FOREIGN KEY تعیین کنیم (هنگام ساخت یک جدول):

در MySQL می گوییم:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

در SQL Server و Oracle و MS Access میگوییم:

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

نکته: اگر می خواهید FOREIGN KEY را در چند ستون اجرا کنید از ساختار زیر برای تمام پایگاه های داده استفاده کنید:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

استفاده از ALTER TABLE

فرض کنید می خواهیم PersonID را به عنوان FOREIGN KEY تعیین کنیم اما جدول Orders از قبل ساخته شده است بنابراین با دستور ALTER TABLE می نویسیم:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

همچنین برای پیاده سازی آن روی چندین ستون مختلف می توانیم از ساختار زیر استفاده کنیم:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

ساختار های بالا بر روی MySQL و SQL Server و Oracle و MS Access جواب می دهند.

حذف FOREIGN KEY

برای حذف FOREIGN KEY در MySQL می توانیم از ساختار زیر استفاده کنیم:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

اما اگر از SQL Server و Oracle و MS Access استفاده می کنید باید پیرو دستور زیر باشید:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

امیدوارم از این قسمت استفاده ی کافی را برده باشید. در قسمت بعد constraint های بیشتر را بررسی می کنیم.