Constraints
- از دستورات تعریف شده Constraints به منظور اعمال قوانین بر روی دیتاهای یک جدول استفاده میکنیم.
- از دستورات Constraints هنگام ساخت جدول و یا اصلاح جدول استفاده میشود:
CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, …. );
- دستورات مربوط به Constraints:
NOT NULL
- استفاده از NOT NULL باعث میشود آن ستون مقادیر NULL را به عنوان ورودی قبول نکند.
- مثال:
در این مثال در جدول sellers باید مشخصات مربوط به فروشندگان مقادیر ID, LastName, FirstName را داشته باشد اما مقدار Age اجباری نیست.
CREATE TABLE Sellers ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );
- حالا ممکن است بخواهیم قانون عدم پذیرش مقدار NULL را برای ستون Age نیز فعال کنیم. با استفاده از دستور Alter table این اصلاحیه را بر روی ستون موردنظر اعمال میکنیم.
- مثال:
در این نمونه روی ستون Age در جدول Sellers اصلاح مورد نظرمان را انجام میدهیم.
ALTER TABLE Sellers MODIFY Age int NOT NULL;
UNIQUE
- با استفاده از دستور UNIQUE میتوانیم اطمینان داشته باشیم که مقادیر وارد شده در یک ستون همگی منحصر به فرد هستند.
- مثال:
در مثال پیش رو هنگام ساخت جدول Sellers برای ستون ID اجباری قرار میدهیم که علاوه بر عدم پذیرش مقدار NULL باید مقادیر موجود در این ستون منحصر به فرد باشند.
CREATE TABLE Sellers ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );
- مثال:
در مثالی دیگر نحوه اعمال قانون UNIQUE را بر روی چندین ستون ID, LastName از جدول sellers میبینیم.
CREATE TABLE Sellers ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Sellers UNIQUE (ID,LastName) );
- در این حالت نیز اگر قصد اعمال قانون UNIQUE را بر روی ستون یک جدول از پیش ساخته شده را دارید میتوانید از دستور Alter table استفاده کنید.
ALTER TABLE Sellers ADD UNIQUE (ID);
- میتوانید با یک دستور قانون UNIQUE را همزمان بر روی چندین ستون اعمال کنید:
ALTER TABLE Sellers ADD CONSTRAINT UC_Sellers UNIQUE (ID,LastName);
- با استفاده از دستور DROP CONSTRAINTS نیز میتوانید اجبار UNIQUE را از روی ستون موردنظرتان حذف کنید.
ALTER TABLE Sellers DROP CONSTRAINT UC_Sellers;
PRIMARY KEY
- Primary key در واقع وظیفه دارد بین رکوردهای یک جدول تمایز ایجاد کند.
- اگر داده های یک ستون به عنوان Primary key تعیین شوند دو خصوصیت دارند، یکی اینکه داده های آن ستون منحصر به فرد هستند و دیگر اینکه مقدار NULL در این ستون قابل پذیرش نیست.
- هر جدول فقط میتواند یک Primary key داشته باشد که آن هم یا مقادیر یک ستون است یا چندین ستون در کنار هم تشکیل Primary key برای یک جدول را میدهند.
- در نمونه پیش رو ID به عنوان Primary key درنظر گرفته شده است.
CREATE TABLE Sellers ( ID int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );
- در نمونه دیگری دو ستون ID, LastName در کنار یکدیگر مقادیر منحصر به فرد تولید کرده و میتوانند نقش Primary key را ایفا کنند:
CREATE TABLE Sellers ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT PK_Sellers PRIMARY KEY (ID,LastName) );
- اگر قصد حذف خاصیت Primary key از یک ستون را دارید میتوانید به روش زیر عمل کنید:
ALTER TABLE Sellers DROP CONSTRAINT PK_Sellers;
FOREIGN KEY
- Foreign key وظیفه ایجاد لینک بین جداول را بر عهده دارد.
- Foreign key به ستون یا مجموعه ای از چند ستون در یک جدول گفته میشود که در جدولی دیگر نقش Primary key را دارند.
- جدولی که Primary key دارد به عنوان جدول والد (parent) و جدولی که Foreign key دارد جدول فرزند (child) شناخته میشوند.
- جدول Customers که مشخصات خریداران را نگهداری میکند و دارای ستونی به نام CustomerID است که این ستون دارای خاصیت منحصر به فرد بودن و عدم پذیرش NULL است و درواقع Primary key محسوب میشود.
- جدول Orders نیز سفارش مشتریان را دربردارد و ستون CustomerID در آن حکم Foreign key را دارد.
- جدول Customers جدول والد (parent) و جدول Orders جدول فرزند (child) نامیده میشوند.
Customers
| CustomerID | LastName | FirstName | Age |
|---|---|---|---|
| 546 | Ebadi | Maryam | 30 |
| 876 | Ahmadi | Leyla | 23 |
| 987 | Rezaei | Hossein | 20 |
Orders
| OrderID | OrderNumber | PersonID |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
- تعیین Foreign key هنگام ساخت جدول:
CREATE TABLE Orders ( OrderID int NOT NULL PRIMARY KEY, OrderNumber int NOT NULL, CustomerID int FOREIGN KEY REFERENCES Customers(CustomerID) );
- تعیین Foreign key بر روی جدول از پیش ساخته شده:
ALTER TABLE Orders ADD CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
- امکان پاک کردن خاصیت Foreign key از روی ستون نیز وجود دارد:
ALTER TABLE Orders DROP CONSTRAINT FK_CustomerOrder;
CHECK
- دستور CHECK به منظور تعیین محدودیت در بازه داده دریافتی مورد استفاده قرار میگیرد مثلا باید مقدار ستون Age در جدول Sellers بالای ۱۸ سال باشد و مقادیر زیر ۱۸ در جدول حق ثبت شدن ندارند.
CREATE TABLE Sellers ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int CHECK (Age>=18) );
- دقت کنید که امکان قرار دادن چندین محدودیت بر روی چند ستون نیز وجود دارد.
CREATE TABLE Sellers ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Sellers CHECK (Age>=18 AND City=‘Tehran‘) );
- میتوان بر روی ستون خاصی از یک جدول از پیش ساخته شده نیز محدودیت مربوط به check را با استفاده از ALTER TABLE اعمال کرد:
ALTER TABLE Sellers ADD CHECK (Age>=18);
- همچنین امکان حذف محدودیت check از روی ستون جدول وجود دارد:
ALTER TABLE Sellers DROP CONSTRAINT CHK_SellersAge;
DEFAULT
- دستور DEFAULT وظیفه دارد مقدار پیش فرض برای رکوردهای ستون خاصی از جدول قرار دهد.
- در مثال زیرنام شهر فروشندگان به صورت پیش فرض تهران در نظر گرفته شده است.
CREATE TABLE Sellers ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255) DEFAULT ‘Tehran’ );
- در این مثال نیز برای تاریخ ثبت سفارش به صورت پیش فرض تاریخ روز ثبت میشود:
CREATE TABLE Orders ( ID int NOT NULL, OrderNumber int NOT NULL, OrderDate date DEFAULT GETDATE() );
- امکان اضافه کردن مقدار default برای ستونی از جدول به شکل زیر وجود دارد:
ALTER TABLE Sellers ADD CONSTRAINT df_City DEFAULT ‘Tehran’ FOR City;
- همچنین امکان حذف مقدار default برای ستون وجود دارد:
ALTER TABLE Sellers ALTER COLUMN City DROP DEFAULT;
INDEX
- دستور index برای رکوردهای یک جدول index قرار میدهد.
- index موجب افزایش واکشی دیتا از جدول میشود و کاربران هیچوقت indexها را نمیبینند فقط سرعت بالای واکشی دیتا را متوجه میشوند.
- البته آپدیت کردن جداول دارای index خیلی طولانی تر از جداول بدون index است چرا که علاوه بر محتوا مقادیر indexها نیز باید آپدیت شوند.
- در نمونه زیر index تحت عنوان index_lastname و بر روی ستون LastName ایجاد میشود.
CREATE INDEX index_lastname ON Sellers (LastName);
- در نمونه دیگری میبینیم که ایندکس گذاری بر روی دو ستون LastName, FirstName و با نام index_name انجام میشود.
CREATE INDEX index_name ON Sellers (LastName, FirstName);
- امکان حذف index از روی ستون جدول وجود دارد:
DROP INDEX table_name.index_name;
AUTO INCREMENT
- دستور Auto-increment به صورت اتوماتیک با ساخت یک سطر جدید برای جدول یک مقدار جدید و منحصر به فرد تولید میکند.
- از دستور Auto-increment میتوان برای ساخت primary key استفاده کرد چرا که مطمئنیم با ایجاد سطر جدید یک مقدار جدید و منحصر بفرد نیز توسط این دستور برای یطر جدید تولید میشود.
- در نمونه زیر یک جدول تحت عنوان Sellers ساخته میشود که در آن یک ستون تحت عنوان SellerID و با نقش PrimaryKey تعریف میشود، دستور IDENTITY به منظور اعمال
AUTO INCREMENT مورد استفاده قرار گرفته است که در آن مقداردهی از عدد ۱ بوده و در هر مرحله ۲ واحد به عدد قبلی اضافه میشود.
CREATE TABLE Sellers ( SellerID int IDENTITY(1,2) PRIMARY KEY, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );