اعتبارسنجی داده در SQL Server با Check Constraint

اعتبارسنجی دقیق داده‌ها در SQL Server با محدودیت CHECK

محدودیت CHECK در SQL Server ابزاری قدرتمند برای اطمینان از صحت و یکپارچگی داده‌ها در پایگاه داده است. این محدودیت به شما امکان می‌دهد قوانینی را تعریف کنید که داده‌های ورودی به یک ستون یا ترکیبی از ستون‌ها باید از آن‌ها پیروی کنند. در واقع، CHECK Constraint تضمین می‌کند که تمام مقادیر ذخیره شده در یک ستون، شرایط منطقی مشخصی را برآورده می‌کنند. این موضوع در حفظ کیفیت داده‌ها و اجرای قوانین کسب‌وکار حیاتی است.

محدودیت CHECK چیست؟

محدودیت CHECK نوعی محدودیت یکپارچگی (Integrity Constraint) است که برای محدود کردن دامنه مقادیر مجاز در یک ستون یا مجموعه‌ای از ستون‌ها استفاده می‌شود. این محدودیت یک عبارت بولی را تعریف می‌کند و هرگاه تلاشی برای درج یا به‌روزرسانی داده‌ای انجام شود، SQL Server این عبارت را ارزیابی می‌کند. اگر عبارت به TRUE یا UNKNOWN ارزیابی شود، عملیات مجاز است؛ در غیر این صورت (اگر به FALSE ارزیابی شود)، عملیات با خطا مواجه شده و داده‌ها پذیرفته نمی‌شوند. این مکانیسم از ورود داده‌های نامعتبر به جداول پایگاه داده جلوگیری می‌کند و به حفظ سلامت کلی داده‌ها کمک می‌کند.

ایجاد محدودیت CHECK در SQL Server

محدودیت CHECK را می‌توان در زمان ایجاد جدول یا پس از آن با استفاده از دستور ALTER TABLE اضافه کرد.

در زمان ایجاد جدول

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

مثال ۱: محدودیت CHECK در سطح ستون

در این مثال، محدودیت CHECK مستقیماً روی ستون Quantity اعمال می‌شود تا اطمینان حاصل شود که همیشه یک عدد مثبت است.

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Quantity INT
    CONSTRAINT CK_Quantity_Positive CHECK (Quantity > 0)
);

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

(Quantity > 0)

مثال ۲: محدودیت CHECK در سطح جدول

این رویکرد زمانی مفید است که محدودیت شامل چندین ستون باشد. در اینجا، محدودیت CHECK در انتهای تعریف جدول مشخص شده است تا اطمینان حاصل شود که ستون Price مقداری بین 0 و 1000 دارد.

CREATE TABLE ProductsWithPrice (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2),
    CONSTRAINT CK_Price_Range CHECK (Price >= 0 AND Price <= 1000)
);

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

(Price >= 0 AND Price <= 1000)

افزودن چندین محدودیت CHECK

می‌توانید چندین محدودیت CHECK را به یک جدول اضافه کنید تا قوانین پیچیده‌تری را اعمال کنید. هر محدودیت به طور مستقل ارزیابی می‌شود.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT CONSTRAINT CK_Age_Range CHECK (Age BETWEEN 18 AND 65),
    Gender VARCHAR(10) CONSTRAINT CK_Gender_Values CHECK (Gender IN ('Male', 'Female', 'Other'))
);

در این مثال، دو محدودیت CHECK برای ستون‌های Age و Gender تعریف شده‌اند. برای Age، شرط زیر تضمین می‌کند که سن در محدوده قابل قبول باشد:

(Age BETWEEN 18 AND 65)

برای Gender، شرط زیر مقادیر مجاز را محدود می‌کند:

(Gender IN ('Male', 'Female', 'Other'))

افزودن محدودیت CHECK با ALTER TABLE

اگر جدولی از قبل وجود دارد و می‌خواهید یک محدودیت CHECK جدید به آن اضافه کنید، از دستور ALTER TABLE استفاده می‌کنید.

ALTER TABLE Orders
ADD CONSTRAINT CK_OrderDate_Valid CHECK (OrderDate <= GETDATE());

در این دستور، محدودیت CK_OrderDate_Valid به جدول Orders اضافه شده است. شرط زیر اطمینان می‌دهد که تاریخ سفارش نمی‌تواند در آینده باشد و در زمان درج یا به‌روزرسانی بررسی می‌شود:

(OrderDate <= GETDATE())

ALTER TABLE Customers
ADD CONSTRAINT CK_City_Valid CHECK (City IN ('New York', 'Los Angeles', 'Chicago'));

در این مورد، محدودیت CK_City_Valid به جدول Customers اضافه شده و شرط زیر تضمین می‌کند که ستون City فقط می‌تواند یکی از سه مقدار مشخص شده را داشته باشد:

(City IN ('New York', 'Los Angeles', 'Chicago'))

حذف محدودیت CHECK

برای حذف یک محدودیت CHECK از یک جدول، از دستور ALTER TABLE DROP CONSTRAINT استفاده کنید و نام محدودیت را مشخص نمایید.

ALTER TABLE Products
DROP CONSTRAINT CK_Quantity_Positive;

این دستور محدودیت CK_Quantity_Positive را از جدول Products حذف می‌کند، که به معنی عدم اعمال شرط زیر بر روی ستون Quantity از این پس است:

(Quantity > 0)

غیرفعال و فعال کردن محدودیت CHECK

می‌توانید یک محدودیت CHECK را به طور موقت غیرفعال کنید تا داده‌ها بدون اعتبارسنجی وارد شوند، و سپس آن را دوباره فعال کنید.

غیرفعال کردن:

ALTER TABLE Employees
NOCHECK CONSTRAINT CK_Age_Range;

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

(Age BETWEEN 18 AND 65)

فعال کردن:

ALTER TABLE Employees
CHECK CONSTRAINT CK_Age_Range;

این دستور محدودیت CK_Age_Range را مجدداً فعال می‌کند. SQL Server پس از فعال‌سازی، داده‌های موجود را اعتبارسنجی نمی‌کند مگر اینکه با WITH CHECK صریحاً مشخص شود.

ALTER TABLE Employees
WITH CHECK CHECK CONSTRAINT CK_Age_Range;

استفاده از WITH CHECK اطمینان می‌دهد که هنگام فعال‌سازی مجدد محدودیت CK_Age_Range، تمامی داده‌های موجود در جدول Employees نیز در برابر شرط زیر اعتبارسنجی شوند:

(Age BETWEEN 18 AND 65)

تغییر نام محدودیت CHECK

برای تغییر نام یک محدودیت CHECK، می‌توانید از رویه سیستمی ذخیره شده sp_rename استفاده کنید.

EXEC sp_rename 'Employees.CK_Age_Range', 'CK_Employee_Age_Range', 'OBJECT';

این دستور نام محدودیت CK_Age_Range در جدول Employees را به CK_Employee_Age_Range تغییر می‌دهد.

مشاهده محدودیت‌های CHECK

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

SELECT
    con.name AS ConstraintName,
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    col.name AS ColumnName,
    con.definition AS ConstraintDefinition,
    con.is_disabled AS IsDisabled
FROM
    sys.check_constraints AS con
INNER JOIN
    sys.tables AS t ON con.parent_object_id = t.object_id
INNER JOIN
    sys.columns AS col ON con.parent_object_id = col.object_id AND con.parent_column_id = col.column_id
ORDER BY
    TableName, ConstraintName;

این کوئری اطلاعات جامعی شامل نام محدودیت، نام شمای جدول، نام جدول، نام ستون مربوطه، تعریف دقیق محدودیت (CHECK) و وضعیت فعال یا غیرفعال بودن محدودیت را از نماهای سیستمی sys.check_constraints، sys.tables و sys.columns استخراج می‌کند.

SELECT
    OBJECT_NAME(parent_object_id) AS TableName,
    name AS ConstraintName,
    definition AS ConstraintDefinition,
    is_disabled
FROM
    sys.check_constraints;

این کوئری ساده‌تر، نام جدول، نام محدودیت CHECK، تعریف آن و وضعیت فعال یا غیرفعال بودن آن را به طور مستقیم از نمای سیستمی sys.check_constraints بازیابی می‌کند.

نکات و بهترین روش‌ها برای استفاده از CHECK Constraint

• اعتبارسنجی داده‌ها: محدودیت CHECK یک راه عالی برای اطمینان از مطابقت داده‌ها با قوانین کسب‌وکار خاص است. به عنوان مثال، می‌توانید اطمینان حاصل کنید که یک ستون Age همیشه مقادیر مثبت دارد یا یک ستون Salary بالاتر از یک حداقل مشخص است.

  ALTER TABLE Employees
  ADD CONSTRAINT CK_Salary_Minimum CHECK (Salary > 50000);

  این محدودیت تضمین می‌کند که هیچ کارمندی با حقوق کمتر از 50000 در جدول Employees ثبت نمی‌شود. شرط آن به صورت زیر است:

  (Salary > 50000)

• عبارات پیچیده: می‌توانید از عبارات پیچیده‌تر شامل توابع، اپراتورهای منطقی و حتی مقادیر برگشتی از توابع سیستمی استفاده کنید، اما باید دقت کنید که این عبارات نباید شامل کوئری‌های فرعی (subqueries) باشند. به عنوان مثال، برای اعتبارسنجی فرمت ایمیل:

  ALTER TABLE Users
  ADD CONSTRAINT CK_Email_Format CHECK (Email LIKE '%@%.%');

  این محدودیت اطمینان می‌دهد که ستون Email دارای فرمت ایمیل پایه است. شرط آن به صورت زیر است:

  (Email LIKE '%@%.%')

• نام‌گذاری واضح: همیشه نام‌های معنی‌دار و توصیفی برای محدودیت‌های CHECK خود انتخاب کنید. این کار مدیریت و اشکال‌زدایی آن‌ها را در آینده آسان‌تر می‌کند.

• تأثیر بر عملکرد: در حالی که محدودیت‌های CHECK برای یکپارچگی داده‌ها ضروری هستند، تعداد زیاد یا پیچیدگی بیش از حد آن‌ها می‌تواند بر عملکرد عملیات INSERT و UPDATE تأثیر بگذارد، زیرا هر بار باید اعتبارسنجی انجام شود. از این رو، استفاده بهینه از آن‌ها توصیه می‌شود.

• جایگزین‌ها: برای اعتبارسنجی‌های پیچیده‌تر که نیاز به منطق برنامه‌نویسی دارند (مانند بررسی داده‌ها در برابر جداول دیگر)، تریگرها (Triggers) یا رویه‌های ذخیره‌شده (Stored Procedures) ممکن است گزینه‌های مناسب‌تری باشند.