انواع constraint ها: NOT NULL و UNIQUE در SQL

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

Constraint ها

constraint در لغت به معنی «قید» یا «محدودیت» است و همانطور که از نامش مشخص است کار تعیین محدودیت و قوانین مربوط به جدول ها را بر عهده دارد.

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

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

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

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

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

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

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

از مهم ترین constraint ها می توان به موارد زیر اشاره کرد:

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

در این قسمت و قسمت آینده می خواهیم تک تک این موارد را بررسی کنیم.

NOT NULL

ستون های یک جدول در حالت پیش فرض می توانند مقدار NULL را دریافت کنند اما اگر از NOT NULL استفاده کنید متوجه می شوید که دیگر ستون هایتان نمی توانند مقادیر NULL بپذیرند.

اگر چنین کاری را انجام دهید دیگر نمی توانید ردیف های جدیدی را اضافه کنید که در آن ها مقدار NULL داشته باشیم و یا دیگر نمی توانید ردیف ها را طوری ویرایش کنید که مقدار NULL در یکی از فیلد ها بماند.

مثال: در کد زیر قصد داریم ستون های ID و LastName و FirstName را طوری تعیین کنیم که مقدار NULL را قبول نکنند:

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

اجرای این کد در پایگاه داده ی Northwind

اما اگر نخواهید مثل کد بالا یک جدول را از نو بسازید، می توانید جدول فعلی خود را ویرایش کنید. به عنوان مثال من ستون Age در جدول Persons را ویرایش می کنم تا مقادیر NULL را قبول نکند:

ALTER TABLE Persons
MODIFY Age int NOT NULL;

سوال: NOT NULL چه استفاده ی عملی دارد؟

پاسخ: بعضی از ستون های شما نباید خالی باشند؛ به طور مثال اگر در جدول کاربرانتان ستونی با نام UserName داشته باشید نباید به کاربر اجازه دهید بدون Username باشند چرا که یوزرنیم هر کاربر مانند شناسنامه ی آن کاربر در سایت شما است! این موضوع در برخی موارد به سلیقه ی شما هم بستگی دارد به طور مثال اگر دوست دارید شماره تلفن کاربرانتان را داشته باشید و از سیستم تایید هویت از طریق SMS عمل می کنید، می توانید مقدار ستون PhoneNumber را به صورت NOT NULL قرار دهید تا مطمئن شوید تمام کاربران شماره ی خود را وارد کرده اند (البته تمام این موارد باید ابتدا در سطح front-end و توسط جاوا اسکریپت و سپس PHP و دیگر زبان ها چک شوند).

UNIQUE

UNIQUE در لغت به معنی «منحصر به فرد» و غیر تکراری است. مطمئن هستم که از معنی آن متوجه کارش شده اید. اگر از UNIQUE برای ستون خاصی استفاده کنید، آن ستون نمی تواند مقادیر تکراری داشته باشد و باید تک تک فیلد هایش با هم فرق کنند.

البته PRIMARY KEY نیز درونِ خودش UNIQUE را دارد اما تفاوت های مهمی با UNIQUE دارند که بعدا توضیح خواهیم داد. به طور مثال می توانید چندین ستونِ UNIQUE داشته باشید اما فقط می توانید یک عدد PRIMARY KEY داشته باشید دلیل اش هم این است که این دو استفاده ی کاملا متفاوتی دارند و به جای هم به کار نمی روند.

ساختار کلی برای استفاده از UNIQUE به این شکل است:

برای SQL Server و Oracle و MS Access:

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

برای MySQL:

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

در ضمن اگر می خواهید UNIQUE را بر روی چندین ستون تنظیم کنید از دستور زیر استفاده کنید:

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

این دستور برای MySQL و SQL Server و Oracle و MS Access یکسان است.

اگر بخواهید از دستور ALTER TABLE استفاده کنید و جدول جدیدی نسازید چطور؟ به طور مثال فرض کنید می خواهیم ستون ID را که از قبل ساخته شده تغییر دهیم. در این صورت مینویسیم:

ALTER TABLE Persons
ADD UNIQUE (ID);

حالا اگر بخواهیم علاوه بر ID ستون LastName را نیز منحصر به فرد کنیم، می گوییم:

ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

اگر زمانی ستونی را UNIQUE کردید اما بعدا پشیمان شدید می توانید با دستورات زیر آن را از حالت UNIQUE خارج کنید:

برای MySQL:

ALTER TABLE Persons
DROP INDEX UC_Person;

برای SQL Server و Oracle و MS Access:

ALTER TABLE Persons
DROP CONSTRAINT UC_Person;

سوال: کاربرد عملی UNIQUE چیست؟

پاسخ: بیایید به مثال NOT NULL برگردیم؛ گفتیم فیلدی مانند یوزرنیم یا ID نباید خالی باشد. حالا فکر کنید: «آیا ID می تواند تکراری باشد؟» جواب قطعا خیر است! ID یا یوزرنیم به معنای شناسه ی کاربری است! همانطور که هیچ کاربری در سایت نباید بدون شناسنامه باشد، شناسنامه ی هیچ دو کاربری نیز نباید یکسان باشد. هر زمان که خواستید فیلد مورد نظرتان خاص یک کاربر یا یک چیز باشد می توانید از UNIQUE استفاده کنید.

امیدوارم از این قسمت لذت برده باشید.