انواع کلید در SQL

در آموزش ساخت جدول با انواع داده و دستور ایجاد جدول در sql آشنا شدید. در این آموزش با انواع کلید در sql آشنا خواهید شد و یاد خواهید گرفت که در sql می‌توان به کمک قیدها (Constraint) یکسری قوانین برای داده‌ها در هنگام ساخت جدول تعیین کرد که پایگاه داده بصورت اتومات صحت اطلاعات ورودی را بررسی کند که اگر بین محدودیت‌های تعیین شده و اطلاعات وارد شده مغایرت وجود داشته باشد این محدودیت اجازه ورود اطلاعات غیر مجاز را ندهد.

انواع constraint در پایگاه داده

انواع کلید در sql وجود دارد که شش قید به شرح زیر می‌باشد:

Not Null در sql

بصورت پیش فرض هر فیلد امکان پذیرش مقدار Null را دارد در واقع Allow Null یا Nullable است. برای جلوگیری از پذیرش مقدار Null باید مقابل فیلد موردنظر عبارت “Not Null” را قرار دهید.

create table Person
(
Code int identity,
FirstName nvarchar(30),
LastName nvarchar(50) not null
)

دستور default در sql

برای هر فیلد می‌توان با استفاده از این قید (Constraint) یک مقدار پیش فرض در نظر گرفت که درصورتیکه برنامه‌نویس به این فیلد مقدار ندهد مقدار این فیلد با استفاده از مقدار پیش فرض (default) مقداردهی خواهد شد.

اگر یک فیلد دارای مقدار default باشد و از طرفی امکان پذیرش Null هم داشته باشد اگر هیچ مقداری به آن فیلد اختصاص داده نشود مقدار default اختصاص داده می‌شود اما اگر مقدار Null برای این فیلد قرار داده شود اینبار مقدار defualt قرار داده نخواهد شد و مقدار فیلد Null خواهد بود.

درصورتیکه یک فیلد دارای مقدار Default باشد و از طرفی امکان پذیرش Null نداشته باشد در اینصورت اگر مقداری برای این فیلد قرار داده نشود مقدار default به آن فیلد اختصاص داده می‌شود و اگر به فیلد مقدار Null داده شود خطای “column does not allow nulls” مشاهده خواهد شد.

می‌توان در هنگام Insert بجای مقدار فیلد موردنظر از کلمه کلیدی default برای اختصاص مقدار پیش فرض استفاده کرد البته نوشتن این کلمه کلیدی الزامی نیست و تفاوتی بین دو دستور Insert زیر نیست. توجه داشته باشید که نوشتن default به تنهایی ملاک بی‌مقدار ماندن یک فیلد نیست بلکه باید مقدار پیش فرض را بعد از کلمه کلیدی default بصورت زیر مشخص کنید.

create table users
(
guid uniqueidentifier default newId() not null,
begindate datetime default getdate(),
userid int 
)

insert users
values (default,null,1)

insert users(begindate,userid)
values (null,1)

در مثال فوق فیلد guid برابر با مقدار پیش فرض، فیلد begindate برابر با Null و فیلد userid برابر با مقدار 1 قرار داده می‌شود و این رکورد در جدول بدین صورت ذخیره خواهد شد. دقت داشته باشید که بعد از قید default نمی‌توان از سایر فیلدها استفاده کرد. دستور زیر اشتباه است و پس از اجرا شدن در sql خطا رخ می‌دهد.

create table Users
(
guid uniqueidentifier default newId() not null,
begindate datetime default getdate(),
userid int,
UserName nvarchar(30),
FullName nvarchar(max) default UserName
)

دستور check در sql

قید Check اعمال شرایط برای اعتبارسنجی مقادیر ورودی می‌باشد بطور مثال طول رمز کاربر حداقل 6 کاراکتر باشد. این قید محدود به فیلد خاصی نمی‌باشد و می‌توان در شرایط قید Check چندین شرط قرار داد. همچنین بر روی یک فیلد هم می‌توان چندین شرط قرار داد.

constraint نام قید
check (شرایط)

در دستور زیر برای کلمه عبور، شرط اینکه کلمه عبور باید بزرگتر از 6 کاراکتر باشد قرار داده شده است و پس از اجرای دستور insert که در زیر نوشته شده است خطا رخ خواهد داد و عملیات درج در جدول انجام نخواهد شد.

create table Users
(
guid uniqueidentifier default newId() not null,
userid int,
UserName nvarchar(30),
[Password] varchar(50) not null,
constraint chk_Password
check(len([Password])>6)
)

insert users
values (default,1,'s.mohamadi','123456')

در قید Check امکان استفاده از سایر فیلدها وجود دارد. بطور مثال یک جدول دارید که دارای تاریخ شروع و پایان است و می‌خواهید چک کنید که تاریخ پایان از تاریخ شروع بزرگتر باشد در غیراینصورت اجازه درج اطلاعات داده نشود و خطا رخ دهد.

create table product
(
Id int identity,
ProductName varchar(max),
BeginDate datetime default getdate() not null,
EndDate datetime,
constraint chk_product
check(EndDate>BeginDate)
)

بررسی انواع کلید در sql

دستور unique در sql

قید Unique برای اینکه یک فیلد مقادیر تکراری نپذیرد استفاده می‌شود و در واقع مقادیر فیلد موردنظر منحصر به فرد و یکتا است.

create table Person
(
code int identity,
FirstName nvarchar(50),
LastName nvarchar(max) not null,
CodeMelli varchar(10) unique not null
)

می‌توان قید Unique را بر روی بیش از یک فیلد نیز اعمال کرد.

create table Person
(
code int identity,
FirstName nvarchar(50),
LastName nvarchar(max) not null,
CodeMelli varchar(10) unique not null,
Email varchar(100) unique
)

در مثال بالا کاربر نمی‌تواند ایمیل تکراری یا کد ملی تکراری در سیستم وارد کند و اگر یکی از آنها نیز تکراری باشد خطا رخ می‌دهد و اجازه درج اطلاعات داده نمی‌شود. این یکتا بودن در خصوص مقدار Null نیز وجود دارد یعنی تنها یک رکورد می‌تواند فیلد email برابر با Null باشد.

درصورتیکه قید unique بر روی بیش از یک فیلد بصورت زیر نوشته شود یعنی ترکیب این فیلدها نباید تکراری باشد در مثال زیر نباید در سیستم برای کد ملی وارد شده دقیقاً همان ایمیل وارد شده باشد یعنی اگر کد ملی “1111111111” با ایمیل “a.a@yahoo.com” در سیستم ثبت شده امکان درج مجدد آنها وجود ندارد اما کاربر می‌تواند همان کد ملی “1111111111” را با ایمیل “b.b@yahoo.com” درج کند و خطایی رخ نمی‌دهد اما در حالت بالا مشاهده کردید که هر یک از فیلدها باید به تنهایی باید منحصر به فرد باشند و حالت ترکیبی ندارند.

create table Person
(
code int identity,
FirstName nvarchar(50),
LastName nvarchar(max) not null,
CodeMelli varchar(10) not null,
Email varchar(100)
constraint uq_product 
unique (CodeMelli,Email)
)

محدودیت کلید اصلی در sql

کلید اصلی یکی از انواع کلید در sql می‌باشد که یک روش ایجاد دسترسی منحصر به فرد به هر رکورد است. در تعاریف سنتی کلید اصلی فیلد یا مجموعه‌ای از فیلدهاست که حتماً مقدار دارند و در رکوردها مقدار تکراری ندارند.
کلید اصلی به هیچ عنوان نباید از اطلاعات کاربر باشد و باید یک مقدار int بصورت identity و یا یک مقدار از نوع uniqueidentifier تعریف شود و به عنوان کلید اصلی قرار داده شود.

به سه حالت می‌توان کلید اصلی را مشخص کرد:

• اگر یک فیلد به عنوان کلید در نظر می‌گیریم و اسم قید کلید اهمیت ندارد مقابل فیلد مورد نظر عبارت “primary key” نوشته می‌شود.

create table Person
(
PersonGuid uniqueidentifier default newid() primary key,
code int identity,
FirstName nvarchar(50),
LastName nvarchar(max) not null,
CodeMelli varchar(10) not null,
Email varchar(100)
)

• اگر یک یا چند فیلد به عنوان کلید استفاده شود و اسم قید اهمیتی ندارد بصورت زیر کلیدهای اصلی را می‌توان تعریف کرد.

create table Person
(
PersonGuid uniqueidentifier default newid(),
code int identity,
FirstName nvarchar(50),
LastName nvarchar(max) not null,
CodeMelli varchar(10) not null,
Email varchar(100)
primary key (PersonGuid,code)
)

• اگر یک یا چند فیلد به عنوان کلید استفاده شود و بخواهیم اسم قید هم مشخص شود بصورت زیر کلیدهای اصلی را می‌توان تعریف کرد.

create table Person
(
PersonGuid uniqueidentifier default newid(),
code int identity,
FirstName nvarchar(50),
LastName nvarchar(max) not null,
CodeMelli varchar(10) not null,
Email varchar(100)
constraint pk_person
primary key (PersonGuid,code)
)

ساخت کلید خارجی در sql server

کلید خارجی یکی دیگر از انواع کلید در sql است. این قید برای ایجاد ارتباط بین جداول ایجاد می‌شود. در مثال زیر یک جدول کالا و جدول فروش تعریف شده که در واقع جدول کالا به عنوان جدول پدر می‌باشد و جدول فروش جدول فرزند است که دارای کلید خارجی است.

create table Product 
(
Guid uniqueidentifier primary key,
id int identity,
ProductName nvarchar(200)
)

create table sales
(
Guid uniqueidentifier primary key,
productGuid uniqueidentifier foreign key references product (Guid), 
code int identity,
Quantity int,
price float
)

کلید خارجی یکسری قوانین را به شرح زیر پیاده‌سازی می‌کند:

• درج رکورد فرزند (جدولی که کلید خارجی مرتبط با جدول مرجع است) بدون وجود پدر (جدولی که از طریق کلید اصلی با کلید خارجی جدول فرزند در ارتباط است). بطورمثال امکان ثبت آمار فروش برای کالایی که اطلاعات آن در جدول Product ثبت نشده است وجود ندارد.

• امکان حذف رکورد پدری که دارای فرزند است وجود ندارد بطورمثال اگر کالایی فروش رفته باشد امکان حذف کالا وجود ندارد مگر اینکه با دستور “on delete cascade” این حالت فعال شود که اگر کالایی فروش رفته است در صورت حذف کالا بصورت خودکار تمامی آمار فروش آن کالا نیز حذف شود.

create table sales
(
Guid uniqueidentifier primary key,
productGuid uniqueidentifier foreign key references product (Guid) on delete cascade, 
code int identity,
Quantity int,
price float
)

• امکان ویرایش فیلد ارتباطی پدر با فرزند در پدران دارای فرزند وجود ندارد. بطورمثال امکان ویرایش فیلد Guid در جدول کالا که دارای آمار فروش است وجود ندارد مگر اینکه با دستور “on update cascade” این حالت فعال شود که اگر کالایی فروش رفته است در صورت ویرایش فیلد ارتباطی کالا (بطورمثال Guid) بصورت خودکار فیلد ProductGuid تمامی آمار فروش آن کالا نیز ویرایش شود.

create table sales
(
Guid uniqueidentifier primary key,
productGuid uniqueidentifier foreign key references product (Guid) on update cascade, 
code int identity,
Quantity int,
price float
)

می‌توان هر دو دستور ” on delete cascade ” و ” on update cascade ” را برای یک فیلد اضافه کرد.

create table sales
(
Guid uniqueidentifier primary key,
productGuid uniqueidentifier foreign key references product (Guid) on delete cascade on update cascade, 
code int identity,
Quantity int,
price float
)