آموزش جامع SQL Server (جلسه ۱۶: ویژگی‌ها)
1398/03/20 14:15 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۱۶: ویژگی‌ها)

در SQL Server می‌توانید برای داده‌ها یا ستون‌های جداول ویژگی‌هایی اعمال کنید که این ویژگی‌ها عبارت‌اند از:

  • کلید اصلی: مفهوم کلید اصلی را توضیح می‌دهد و نشان می‌دهد که چگونه می‌توانید از ویژگی کلید اصلی برای مدیریت یک کلید اصلی از یک جدول استفاده کنید.

  • کلید خارجی: مفهوم کلید خارجی را توضیح می‌دهد و نشان می‌دهد چگونه می‌توانید از ویژگی کلید خارجی برای تقویت ارتباط داده‌ها در دو جدول استفاده کنید.

  • ویژگی NOT NULL: چگونگی اطمینان یافتن از اینکه یک ستون هیچ‌گاه مقدار NULL نپذیرد را توضیح می‌دهد.

  • ویژگی UNIQUE: اطمینان می‌دهد که داده‌های موجود در یک ستون یا یک گروه از ستون‌ها، در بین ردیف‌های یک جدول منحصر به فرد یا unique باشند.

  • ویژگی CHECK: فرآیند اضافه کردن منطق برای بررسی داده‌ها پیش از مرتب کردن آن‌ها در جداول را توضیح می‌دهد.

با ما همراه باشید!

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

کلید اصلی یا Primary Key در SQL Server

کلید اصلی یا Primary key یک ستون یا گروهی از ستون‌ها است که به صورت منحصر به فرد مقدار هر ردیف در یک جدول را مشخص می‌کند. می‌توان با استفاده از ویژگی Primary Key برای یک جدول کلید اصلی ایجاد کرد.

اگر کلید اصلی فقط در یک ستون قرار گیرد، می‌توانید از ویژگی PRIMARY KEY به عنوان یک ویژگی ستون استفاده کنید:

CREATE TABLE table_name (
    pk_column data_type PRIMARY KEY,
    ...
);

در صورتی که کلید اصلی دارای دو یا تعداد بیشتری ستون باشد، باید از ویژگی PRIMARY KEY به عنوان یک ویژگی جدول استفاده کنید:

CREATE TABLE table_name (
    pk_column_1 data_type,
    pk_column_2 data type,
    ...
    PRIMARY KEY (pk_column_1, pk_column_2)
);

هر جدول می‌تواند شامل فقط یک کلید اصلی باشد. همه ستون‌هایی که در ویژگی کلید اصلی قرار می‌گیرند، باید به صورت NOT NULL تعریف شوند. اگر ویژگی NOT NULL را خودتان تعریف نکنید، SQL Server به صورت خودکار آن را برای تمام ستون‌های کلید اصلی تعریف می‌کند.

همچنین وقتی یک کلید اصلی ایجاد می‌کنید، SQL Server به صورت خودکار یک شاخص یا index دسته یا خوشه‌بندی منحصر به فرد (یا یک index غیر خوشه بندی، در صورتی که خودتان این‌طور مشخص کرده باشید) ایجاد می‌کند.

مثال‌های ویژگی PRIMARY KEY در SQL Server

مثال زیر یک جدول با یک کلید اصلی ایجاد می‌کند که شامل یک ستون است:

CREATE TABLE sales.activities (
    activity_id INT PRIMARY KEY IDENTITY,
    activity_name VARCHAR (255) NOT NULL,
    activity_date DATE NOT NULL
);

در جدول sales.activities، ستون activity_id ستون کلید اصلی است. یعنی اینکه ستون activity_id شامل مقادیر منحصر به فرد است.

ویژگی IDENTITY که برای ستون activity_id استفاده شده است، به صورت خودکار مقادیر عددی منحصر به فرد تولید می‌کند.

کد زیر یک جدول جدید به نام Sales.participants ایجاد می‌کند که کلید اصلی آن شامل دو ستون است:

CREATE TABLE sales.participants(
    activity_id int,
    customer_id int,
    PRIMARY KEY(activity_id, customer_id)
);

در این مثال، مقادیر در هر کدام از ستون‌های activity_id یا customer_id می‌توانند تکراری باشند، اما ترکیب مقادیر در همان ستون باید منحصر به فرد باشند.

معمولاً، یک جدول همیشه دارای یک کلید اصلی است که در زمان ایجاد جدول مشخص می‌شود. به هر حال، گاهی یک جدول از پیش موجود، ممکن است دارای یک کلید اصلی از قبل تعریف شده نباشد. در این مورد، می‌توانید با استفاده از دستور ALTER TABLE یک کلید اصلی به آن جدول اضافه کنید. مثال زیر را در نظر بگیرید:

کد زیر یک جدول بدون کلید اصلی ایجاد می‌کند:

CREATE TABLE sales.events(
    event_id INT NOT NULL,
    event_name VARCHAR(255),
    start_date DATE NOT NULL,
    duration DEC(5,2)
);

برای تبدیل ستون event_id به عنوان کلید اصلی، از دستور ALTER TABLE به شکل زیر استفاده می‌کنیم:

ALTER TABLE sales.events 
ADD PRIMARY KEY(event_id);

توجه کنید که اگر جدول Sales.events از قبل داده داشته باشد، یعنی قبل از تبدیل ستون Event_id به کلید اصلی، باید اطمینان حاصل کنید که مقادیر در ستون Event_id حتماً منحصر به فرد باشند.

در نتیجه، چگونگی استفاده از ویژگی PRIMARY KEY برای ایجاد یک کلید اصلی برای یک جدول را آموختید.

کلید خارجی در SQL Server

جداول vendor_groups و vendors در کد زیر را در نظر بگیرید:

CREATE TABLE procurement.vendor_groups (
    group_id INT IDENTITY PRIMARY KEY,
    group_name VARCHAR (100) NOT NULL
);
 
CREATE TABLE procurement.vendors (
        vendor_id INT IDENTITY PRIMARY KEY,
        vendor_name VARCHAR(100) NOT NULL,
        group_id INT NOT NULL,
);

هر فروشنده متعلق به یک گروه فروشنده است و هر گروه فروشنده می‌تواند دارای صفر یا تعداد بیشتری فروشنده باشد. ارتباط بین جداول vendor_groups و vendors یک به چند است.

برای هر ردیف در جدول Vendors، همیشه می‌توانید یک ردیف مشابه در جدول vendor_groups بیابید.

به هر حال، با وضعیت فعلی جداول، می‌توانید یک ردیف به جدول vendors اضافه کنید، بدون اینکه یک جدول مشابه با آن در جدول Vendor_groups وجود داشته باشد.

همچنین می‌توانید یک ردیف در جدول vendor_groups را بدون به روز رسانی یا حذف ردیف‌های مشابه آن در جدول vendors حذف کنید، که در نتیجه ردیف‌های به اصطلاح یتیم شده در جدل vendors به وجود می‌آیند.

برای تقویت ارتباط بین داده‌ها در جداول vendor_groups و vendors، باید یک کلید خارجی در جدول Vendors ایجاد کنید.

کلید خارجی یک ستون یا گروهی از ستون‌ها در یک جدول است که به صورت منحصر به فرد مقدار ردیف از یک جدول دیگر را مشخص می‌کند (یا اگر به خود جدول اشاره شده باشد، مقادیر یک ردیف در همان جدول را مشخص می‌کند).

برای ایجاد یک کلید خارجی، از ویژگی FOREIGN KEY استفاده می‌کنیم.

کد زیر جدول vendors را drop می‌کند و مجدداً آن را با یک ویژگی FOREIGN KEY ایجاد می‌کند:

DROP TABLE vendors;
 
CREATE TABLE procurement.vendors (
        vendor_id INT IDENTITY PRIMARY KEY,
        vendor_name VARCHAR(100) NOT NULL,
        group_id INT NOT NULL,
        CONSTRAINT fk_group FOREIGN KEY (group_id) 
        REFERENCES procurement.vendor_groups(group_id)
);

جدول vendor_groups حالا جدول والد یا parent نام دارد که در واقع جدولی است که کلید خارجی به آن اشاره می‌کند. جدول vendors جدول فرزند نام دارد که در واقع جدولی است که کلید اصلی در آن اعمال شده است.

در کد بالا، دستوری که در کد زیر مشاهده می‌کنید یک ویژگی FOREIGN KEY به نام fk_group ایجاد می‌کند که ستون group_id در جدول vendors را به ستون group_id در جدول vendor_groups متصل می‌کند:

CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES procurement.vendor_groups(group_id)

Syntax ویژگی FOREIGN KEY در SQL Server

Syntax کلی برای ایجاد یک ویژگی FOREIGN KEY به شرح زیر است:

CONSTRAINT fk_constraint_name 
FOREIGN KEY (column_1, column2,...)
REFERENCES parent_table_name(column1,column2,..)

اجازه دهید این syntax را دقیق بررسی کنیم.

ابتدا، نام ویژگی FOREIGN KEY پس از کلید واژه CONSTRAINT مشخص شده است. مشخص کردن نام ویژگی اجباری نیست، در نتیجه می‌توانید ویژگی FOREIGN KEY را به صورت زیر نیز تعریف کنید:

FOREIGN KEY (column_1, column2,...)
REFERENCES parent_table_name(column1,column2,..)

در این صورت، SQL Server به صورت خودکار نامی برای ویژگی FOREIGN KEY ایجاد می‌کند.

سپس، لیستی از ستون‌های کلید خارجی که با ویرگول از هم مجزا شده‌اند در بین پرانتزها پس از کلیدواژه FOREIGN KEY قرار می‌گیرند.

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

مثال ویژگی FOREIGN KEY در SQL Server

اول، چند ردیف به جدول vendor_groups اضافه کنید:

INSERT INTO procurement.vendor_groups(group_name)
VALUES('Third-Party Vendors'),
      ('Interco Vendors'),
      ('One-time Vendors');

دوم، یک فروشنده جدید با یک گروه فروشنده به جدول Vendors اضافه کنید:

INSERT INTO procurement.vendors(vendor_name, group_id)
VALUES('ABC Corp',1);

کد همان‌طور که انتظار می‌رفت عمل کرد.

سوم، سعی کنید یک فروشنده جدید که گروه فروشنده وی در جدول vendor_groups موجود نیست اضافه کنید:

INSERT INTO procurement.vendors(vendor_name, group_id)
VALUES('XYZ Corp',4);

SQL Server ارور زیر را می‌دهد:

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_group". The conflict occurred in database "BikeStores", table "procurement.vendor_groups", column 'group_id'.

 در این مثال، SQL Server به خاطر ویژگی FOREIGN KEY دستور insert را نپذیرفته و ارور می‌دهد.

اعمال ارجاعی یا اشاره‌ای

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

علاوه بر این، در زمانی که ردیف در جدول والد به روز رسانی یا حذف می‌شود، ویژگی کلید خارجی به شما اجازه می‌دهد اعمال ارجاعی را به این شکل تعریف کنید:

FOREIGN KEY (foreign_key_columns)
    REFERENCES parent_table(parent_key_columns)
    ON UPDATE action 
    ON DELETE action;

ON UPDATE و ON DELETE مشخص می‌کنند که وقتی یک ردیف در جدول والد به روز رسانی یا حذف می‌شود، کدام عمل اجرا خواهد شد. اعمال مجاز عبارت‌اند از: SET NULL، CASCADE، NO ACTION و SET DEFAULT.

اعمال حذف ردیف‌ها در جدول والد

اگر یک یا تعداد بیشتری ردیف را در جدول والد حذف کنید، می‌توانید یکی از اعمال زیر را به کار ببرید:

  • ON DELETE NO ACTION: سیستم ارور می‌دهد و عمل حذف را به ردیف موجود در جدول والد بازمی‌گرداند.

  • ON DELETE CASCADE: سیستم ردیف‌ها را در جدول فرزند حذف می‌کند که این ردیف‌ها مشابه ردیف‌های حذف شده از جدول والد هستند.

  • ON DELETE SET NULL: اگر ردیف‌های مشابه در جدول والد حذف شده باشند، سیستم مقدار ردیف‌های موجود در جدول فرزند را به NULL تغییر می‌دهد. برای اجرای این عمل، ستون‌های کلید خارجی باید قابلیت null شدن داشته باشند.

  • ON DELETE SET DEFAULT: اگر ردیف‌های مشابه در جدول والد حذف شده باشند، سیستم ردیف‌های موجود در جدول فرزند را به مقادیر پیش‌فرضشان تغییر می‌دهد. برای اجرای این دستور، ستون‌های کلید خارجی باید دارای تعاریف پیش‌فرض باشند. توجه کنید که یک ستون با قابلیت NULL شدن دارای مقدار پیش‌فرض NULL است، البته اگر هیچ مقدار پیش‌فرض دیگری برای آن مشخص نشده باشد.

به طور پیش‌فرض، SQL Server در صورتی عمل ON DELETE NO ACTION را انجام می‌دهد که شما عملاً هیچ دستور و عمل خاصی مشخص نکرده باشید.

عمل به روز رسانی ردیف‌ها در جدول والد

اگر یک یا چند ردیف را در جدول والد به روز رسانی کنید، می‌توانید یکی از اعمال زیر را به کار ببرید:

  • ON UPDATE NO ACTION: سیستم یک ارور می‌دهد و عمل به روز رسانی را به ردیف موجود در جدول والد بازمی‌گرداند.

  • ON UPDATE CASCADE: وقتی ردیف‌ها در جدول والد به روز رسانی می‌شوند، سیستم ردیف‌های مشابه در جدول فرزند را نیز به روز رسانی می‌کند.

  • ON UPDATE SET NULL: وقتی ردیفی در جدول والد به روز رسانی شود، سیستم مقدار ردیف‌های مشابه موجود در جدول فرزند را به NULL تغییر می‌دهد. توجه کنید که برای اجرای این عمل، ستون‌های کلید خارجی باید قابل NULL شدن باشند.

  • ON UPDATE SET DEFAULT: سیستم برای ردیف‌هایی در جدول فرزند، که ردیف‌های مشابه آن‌ها در جدول والد به روز رسانی شده‌اند، مقدار پیش‌فرض قرار می‌دهد.

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

ویژگی NOT NULL در SQL Server

ویژگی NOT NULL در SQL Server به زبان ساده، مشخص می‌کند که یک ستون هیچ‌وقت نباید مقدار NULL داشته باشد.

مثال زیر جدولی با ویژگی NOT NULL برای ستون‌های last_name، first_name و email ایجاد می‌کند:

CREATE SCHEMA hr;
GO
 
CREATE TABLE hr.persons(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(20)
);

توجه کنید که ویژگی‌های NOT NULL همیشه به عنان ویژگی‌های ستون نوشته می‌شوند.

اگر ویژگی NOT NULL را مشخص نکرده باشید، به طور پیش‌فرض SQL Server به ستون اجازه می‌دهد مقدار NULL بپذیرد. در این مثال، ستون phone می‌تواند مقدار NULL بپذیرد.

اضافه کردن NOT NULL به یک ستون موجود

برای اضافه کردن ویژگی NOT NULL به یک ستون موجود، مراحل زیر را انجام دهید:

اول، جدول را به روز رسانی کنید تا هیچ مقدار NULL در ستون وجود نداشته باشد:

UPDATE table_name
SET column_name = <value>
WHERE column_name IS NULL;

دوم، از دستور ALTER TABLE برای تغییر ویژگی ستون استفاده کنید:

ALTER TABLE table_name
ALTER COLUMN column_name data_type NOT NULL;

برای مثال، برای اضافه کردن ویژگی NOT NULL به ستون phone از جدل hr.persons، باید از کدهای زیر استفاده کنید:

ابتدا، اگر یک شخص شماره تلفن ندارد، پس شماره تلفن را به شماره تلفن شرکت (مثلاً4567 123(408)) به روز رسانی کنید:

UPDATE hr.persons
SET phone = "(408) 123 4567"
WHER phone IS NULL;

سپس، ویژگی ستون phone را اصلاح کنید:

ALTER TABLE hr.persons
ALTER COLUMN phone VARCHAR(20) NOT NULL;

حذف ویژگی NOT NULL

برای حذف ویژگی NOT NULL از یک ستون، از دستور ALTER TABLE ALTER COLUMN به شکل زیر استفاده می‌کنیم:

ALTER TABLE table_name
ALTER COLUMN column_name data_type NULL;

برای مثال، برای حذف ویژگی NOT NULL از ستون phone، از دستور زیر استفاده می‌کنیم:

ALTER TABLE hr.pesons
ALTER COLUMN phone VARCHAR(20) NULL;

به این صورت، چگونگی استفاده از ویژگی NOT NULL برای اطمینان از مقدار NULL نپذیرفتن یک ستون آشنا شدید.

ویژگی UNIQUE در SQL Server

ویژگی UNIQUE در SQL Server به شما امکان می‌دهد تا اطمینان حاصل کنید که داده‌های ذخیره شده در یک ستون یا در گروهی از ستون‌ها، در بین ردیف‌های یک جدول منحصر به فرد باشند.

کد زیر جدولی ایجاد می‌کند که داده‌های آن در ستون email در بین ردیف‌های جدول hr.persons منحصر به فرد است:

CREATE SCHEMA hr;
GO
 
CREATE TABLE hr.persons(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

در این syntax، ویژگی UNIQUE به عنوان یک ویژگی ستون تعریف می‌شود. همچنین می‌توانید ویژگی UNIQUE را به عنوان یک ویژگی جدول نیز تعریف کنید:

CREATE TABLE hr.persons(
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    UNIQUE(email)
);

SQL Server به صورت خودکار یک index یا شاخص UNIQUE ایجاد می‌کند تا از منحصر به فرد بودن داده‌های ذخیره شده در ستون‌هایی که در ویژگی UNIQUE از آن‌ها نام برده شده است اطمینان حاصل کند.

در نتیجه، اگر سعی کنید یک ردیف تکراری insert کنید، SQL Server تغییر را نمی‌پذیرد و یک پیغام ارور نمایش می‌دهد که می‌گوید ویژگی UNIQUE نقض شده است.

کد زیر یک ردیف جدید به جدول hr.persons اضافه می‌کند:

INSERT INTO hr.persons(first_name, last_name, email)
VALUES('John','Doe','j.doe@bike.stores');

کد همان‌طور که انتظار می‌رفت عمل کرد. به هر حال، کد زیر هم نمی‌تواند ایمیل تکراری را وارد کند:

INSERT INTO hr.persons(first_name, last_name, email)
VALUES('Jane','Doe','j.doe@bike.stores');

SQL Server ارور زیر را می‌دهد:

Violation of UNIQUE KEY constraint 'UQ__persons__AB6E616417240E4E'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is (j.doe@bike.stores).

اگر یک نام مجزا برای ویژگی UNIQUE مشخص نکنید، SQL Server به صورت خودکار یک نام برای آن ایجاد می‌کند. در این مثال، نام ویژگی UQ_persons_AB6E616417240E4E است که خیلی خوانا و جالب نیست.

برای مشخص کردن یک نام خاص به یک ویژگی UNIQUE، از کلید واژه CONSTRAINT به شکل زیر استفاده می‌کنیم:

CREATE TABLE hr.persons (
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    CONSTRAINT unique_email UNIQUE(email)
);

مزایای انتخاب یک نام خاص برای یک ویژگی UUNIQUE عبارت‌اند از:

  • طبقه‌بندی پیام ارور آسان‌تر می‌شود.
  • وقتی می‌خواهید ویژگی را تغییر ‌دهید، می‌توانید به نام آن اشاره می‌کنید.

ویژگی UNIQUE در برابر ویژگی PRIMARY KEY

اگرچه هر دو ویژگی‌های UNIQUE و PRIMARY KEY از  منحصر به فرد بودن داده‌ها اطمینان حاصل می‌کنند، اما وقتی می‌خواهید از منحصر به فرد بودن یک ستون یا گروهی از ستون‌ها که هیچ‌کدام ستون‌های کلید اصلی نیستند اطمینان حاصل کنید، بهتر است به جای ویژگی PRIMARY KEY از ویژگی UNIQUE استفاده کنید.

ویژگی UNIQUE بر خلاف ویژگی PRIMARY KEY، امکان استفاده از مقدار NULL را می‌دهد. علاوه بر این، ویژگی‌های UNIQUE با مقدار NULL به عنوان یک مقدار معمولی رفتار می‌کنند، در نتیجه، به ازای هر ستون فقط اجازه استفاده از یک NULL را می‌دهند.

کد زیر ردیفی را اضافه می‌کند که مقدار آن در ستون email برابر با null است:

INSERT INTO hr.persons(first_name, last_name)
VALUES('John','Smith');

حالا اگر سعی کنید یک NULL دیگر به ستون email اضافه کنید، سیستم به شما ارور خواهد داد:

INSERT INTO hr.persons(first_name, last_name)
VALUES('Lily','Bush');

خروجی به این شکل است:

Violation of UNIQUE KEY constraint 'UQ__persons__AB6E616417240E4E'. Cannot insert duplicate key in object 'hr.persons'. The duplicate key value is (<NULL>).

استفاده از ویژگی‌ UNIQUE برای گروهی از ستون‌ها

برای تعریف یک ویژگی UNIQUE برای گروهی از ستون‌ها، یک ویژگی جدول که در آن نام جداول با ویرگول از هم جدا شده‌اند را به شکل زیر می‌نویسیم:

CREATE TABLE table_name (
    key_column data_type PRIMARY KEY,
    column1 data_type,
    column2 data_type,
    column3 data_type,
    ...,
    UNIQUE (column1,column2)
);

مثال زیر یک ویژگی UNIQUE ایجاد می‌کند که شامل دو ستون person_id و skill_id است:

CREATE TABLE hr.person_skills (
    id INT IDENTITY PRIMARY KEY,
    person_id int,
    skill_id int,
    updated_at DATETIME,
    UNIQUE (person_id, skill_id)
);

اضافه کردن ویژگی UNIQUE به ستون‌های از پیش موجود

وقتی یک ویژگی UNIQUE به یک ستون از پیش موجود یا گروهی از ستون‌ها در یک جدول اضافه می‌کنید، SQL Server ابتدا داده‌های موجود در این ستون‌ها را بررسی می‌کند تا اطمینان حاصل کند که همه مقادیر منحصر به فرد هستند.

اگر SQL Server مقادیر تکراری بیابد، آنگاه ارور می‌دهد و ویژگی UNIQUE را اضافه نمی‌کند.

کد زیر syntax اضافه کردن یک ویژگی UNIQUE به یک جدول را نشان می‌دهد:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name 
UNIQUE(column1, column2,...);

فرض کنید جدول hr.persons را دارید:

CREATE TABLE hr.persons (
    person_id INT IDENTITY PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(20),
);  

کد زیر یک ویژگی UNIQUE به ستون email اضافه می‌کند:

ALTER TABLE hr.persons
ADD CONSTRAINT unique_email UNIQUE(email);

به طور مشابه، کد زیر یک ویژگی UNIQUE به ستون phone اضافه می‌کند:

ALTER TABLE hr.persons
ADD CONSTRAINT unique_phone UNIQUE(phone); 

حذف ویژگی UNIQUE

برای حذف یک ویژگی UNIQUE، از کد ALTER TABLE DROP CONSTRAINT به شکل زیر استفاده می‌کنیم:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

کد زیر ویژگی unique_phone را از جدول hr.person حذف می‌کند:

ALTER TABLE hr.persons
DROP CONSTRAINT unique_phone;

اصلاح ویژگی UNIQUE

SQL Server هیچ دستور مستقیمی برای اصلاح یا تغییر یک ویژگی UNIQUE ندارد، در نتیجه اگر می‌خواهید ویژگی را تغییر دهید، اول باید ویژگی را حذف کنید و سپس مجدداً آن را ایجاد کنید.

به این شکل، چگونگی استفاده از ویژگی UNIQUE برای اطمینان حاصل کردن از اینکه داده‌های قرار گرفته در یک ستون یا گروهی از ستون‌ها منحصر به فرد هستند، آشنا شدید.

ویژگی CHECK در SQL Server

ویژگی CHECK به شما امکان می‌دهد تا مقادیر ستونی که باید نتایج یک کد Boolean را نشان دهد را مشخص کنید.

برای مثال، برای به دست آوردن قیمت‌های واحد مثبت، می‌توانید از کد زیر استفاده کنید:

CREATE SCHEMA test;
GO
 
CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CHECK(unit_price > 0)
);

همان‌طور که مشاهده می‌کنید، تعریف ویژگی CHECK بعد از نوع داده آمده است. تعریف آن شامل کلید واژه CHECK است که پس از آن یک عبارت منطقی در بین پرانتز‌ها قرار می‌گیرد:

CHECK(unit_price > 0)

همچنین می‌توانید با استفاده از کلیدواژه CONSTRAINT به شکل زیر، یک نام مجزا برای آن مشخص کنید:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CONSTRAINT positive_price CHECK(unit_price > 0)
);

نام‌های خاص به طبقه‌بندی پیام‌های ارور کمک کرده و به شما اجازه می‌دهند تا وقتی می‌خواهید ویژگی را تغییر دهید، راحت به نام آن اشاره کنید.

اگر نام ویژگی را به این شکل مشخص نکنید، SQL Server به صورت خودکار یک نام برای آن تعیین می‌کند.

کد insert زیر را مشاهده کنید:

INSERT INTO test.products(product_name, unit_price)
VALUES ('Awesome Free Bike', 0);

SQL Server ارور زیر را می‌دهد:

The INSERT statement conflicted with the CHECK constraint "positive_price". The conflict occurred in database "BikeStores", table "test.products", column 'unit_price'.

ارور به این دلیل اتفاق افتاد که قیمت واحد همان‌طور که در ویژگی CHECK تعریف شده است، بزرگ‌تر از صفر نیست.

کد زیر درست عمل می‌کند، چون عبارت منطقی تعریف شده در ویژگی CHECK برابر با TRUE خواهد شد:

INSERT INTO test.products(product_name, unit_price)
VALUES ('Awesome Bike', 599);

ویژگی CHECK و NULL در SQL Server

ویژگی CHECK مقادیری که باعث می‌شوند عبارت Boolean برابر با FALSE شوند را نمی‌پذیرد.

چون مقدار NULL برابر با UNKNOWN است می‌توان از آن در عبارت استفاده کرد تا یک ویژگی را دور بزنیم.

برای مثال، می‌توانید محصولی که قیمت واحد آن NULL است را به شکل زیر insert کنید:

INSERT INTO test.products(product_name, unit_price)
VALUES ('Another Awesome Bike', NULL);

خروجی به این شکل است:

(1 row affected)

SQL Server مقدار NULL را به ستون unit_price وارد کرد و هیچ اروری هم بازنگرداند.

برای برطرف کردن این مشکل، باید از یک ویژگی NOT NULL برای ستون unit_price استفاده کنید.

اشاره یا ارجاع ویژگی CHECK به چندین ستون

ویژگی CHECK می‌توانید به چندین ستون اشاره یا ارجاع کند. برای مثال، فرض کنید شما قیمت‌های معمولی و تخفیف خورده را در جدول test.products ذخیره می‌کنید و می‌خواهید اطمینان حاصل کنید که قیمت تخفیف خورده همیشه کمتر از قیمت معمولی باشد:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) CHECK(unit_price > 0),
    discounted_price DEC(10,2) CHECK(discounted_price > 0),
    CHECK(discounted_price < unit_price)
);

دو ویژگی اول برای unit_price و discounted_price باید برایتان آشنا باشند.

سومین ویژگی از یک syntax جدید استفاده می‌کند که به یک ستون خاص متصل نیست. در عوض، به عنوان یک آیتم خط مجزا در لیست ستونی که اجزای آن با ویرگول از هم جدا شده‌اند ظاهر می‌شود.

دو ویژگی ستون اول خب ویژگی‌های ستون هستند، در حالی که ویژگی سوم یک ویژگی جدول است.

توجه کنید که می‌توانید ویژگی‌های ستون را به عنوان ویژگی‌های جدول بنویسید. اما، نمی‌توانید ویژگی‌های جدول را به عنوان ویژگی‌های ستون بنویسید. برای مثال، می‌توانید کد بالا را به شکل زیر بنویسید:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0),
    CHECK(discounted_price > unit_price)
);

یا حتی:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0 AND discounted_price > unit_price)
);

همچنین می‌توانید یک نام به ویژگی جدول اختصاص دهید، درست همان‌طور که برای ویژگی ستون نام مشخص کردید:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2),
    discounted_price DEC(10,2),
    CHECK(unit_price > 0),
    CHECK(discounted_price > 0),
    CONSTRAINT valid_prices CHECK(discounted_price > unit_price)
);

اضافه کردن ویژگی CHECK به یک جدول از پیش موجود

برای اضافه کردن یک ویژگی CHECK به یک جدول از پیش موجود، می‌توانید از کد ALTER TABLE ADD CONSTRAINT استفاده کنید.

فرض کنید جدول Test.products را دارید:

CREATE TABLE test.products(
    product_id INT IDENTITY PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    unit_price DEC(10,2) NOT NULL
);

برای اضافه کردن یک ویژگی CHECK به جدول test.products، می‌توانید از کد زیر استفاده کنید:

ALTER TABLE test.products
ADD CONSTRAINT positive_price CHECK(unit_price > 0);

برای اضافه کردن یک ستون جدید با ویژگی CHECK، می‌توانید از کد زیر استفاده کنید:

ALTER TABLE test.products
ADD discounted_price DEC(10,2)
CHECK(discounted_price > 0);

برای اضافه کردن یک ویژگی CHECK به نام valid_price، می‌توانید از کد زیر استفاده کنید:

ALTER TABLE test.products
ADD CONSTRAINT valid_price 
CHECK(unit_price > discounted_price);

حذف ویژگی‌CHECK

برای حذف یک ویژگی CHECK، می‌توانید از دستور ALTER TABLE DROP CONSTRAINT استفاده کنید:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

اگر به ویژگی CHECK یک نام خاص اختصاص دهید، می‌توانید در کد به نام اشاره کنید.

به هر حال، در صورتی که نام خاصی برای ویژگی CHECK مشخص نکرده‌اید، پس باید با استفاده از کد زیر آن را بیابید:

EXEC sp_help 'table_name';

برای مثال:

EXEC sp_help 'test.products';

این کد اطلاعات زیادی از جمله نام ویژگی‌ها را نشان می‌دهد:

نتایج

کد زیر ویژگی positive_price را حذف یا drop می‌کند:

ALTER TABLE test.products
DROP CONSTRAINT positive_price;

غیرفعال کردن ویژگی CHECK برای insert یا update

برای غیر فعال کردن یک ویژگی CHECK برای دستورهای insert یا update، می‌توانید از کد زیر استفاده کنید:

ALTER TABLE table_name
NOCHECK CONSTRAINT constraint_name;

کد زیر ویژگی valid_price را غیر فعال می‌کند:

ALTER TABLE test.products
NO CHECK CONSTRAINT valid_price;

در نتیجه، چگونگی استفاده از ویژگی CHECK برای محدود کردن مقادیری که می‌توانند در یک یا چند ستون از یک جدول insert یا update شوند را آموختید.

خلاصه

در این جلسه به مبحث ساده اما بسیار مهم و حیاتی ویژگی‌ها یا Constraintها پرداختیم، انواع این ویژگی‌ها را معرفی کردیم و به صورت مثال محور توضیح دادیم. ویژگی‌ها در SQL Server عبارت‌اند از:

در جلسه آینده در مورد آخرین موضوعات آشنایی با اصول اولیه SQL Server یعنی عبارت‌ها (Expressionها) و چند نکته مفید صحبت خواهیم کرد و پس از آن وارد مباحث پیچیده‌تر و جذاب‌تر SQL Server خواهیم شد.

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات