اسکریپت‌های بررسی Constraints در SQL Server به‌صورت کامل و آماده

اسکریپت‌های بررسی Constraints در SQL Server به‌صورت کامل و آماده

در SQL Server، مفهوم Constraints یا محدودیت‌ها یکی از مهم‌ترین بخش‌های طراحی و مدیریت پایگاه داده است.
این محدودیت‌ها برای حفظ صحت، یکپارچگی و اعتبار اطلاعات در جداول استفاده می‌شوند.
با کمک Constraintها می‌توان از ثبت داده‌های تکراری، مقادیر نامعتبر، مقادیر NULL در ستون‌های ضروری و ارتباط‌های اشتباه بین جداول جلوگیری کرد.
گاهی لازم است وضعیت Constraintهای موجود در دیتابیس را بررسی کنیم؛
برای مثال، بدانیم کدام جدول دارای کلید اصلی (Primary Key) است،
چه ستون‌هایی محدودیت UNIQUE دارند،
کدام ستون‌ها از CHECK Constraint استفاده می‌کنند یا چه ارتباط‌هایی از طریق FOREIGN KEY بین جداول تعریف شده‌اند.

 

🌟 آیا می‌خواهید به یک متخصص پایگاه داده تبدیل شوید و در دنیای فناوری اطلاعات بدرخشید؟

با دوره آموزشی SQL Server ما، شما می‌توانید به راحتی و با روشی عملی، تمام مهارت‌های لازم را یاد بگیرید!

این دوره به شما آموزش می‌دهد که چگونه داده‌ها را به بهترین شکل مدیریت کنید، گزارش‌های قدرتمند بسازید و به تحلیل‌های عمیق دست یابید.

با محتوای جذاب و پروژه‌های واقعی، شما نه تنها تئوری را یاد می‌گیرید، بلکه توانایی‌های عملی خود را نیز تقویت می‌کنید.

پس فرصت را از دست ندهید! همین امروز به جمع یادگیرندگان ما بپیوندید و اولین قدم را به سوی آینده شغلی روشن‌تر بردارید!

 ⇐همین حالا شروع کنید و به دنیای داده‌ها بپیوندید!

 نمایش تمام Constraintهای دیتابیس

برای مشاهده محدودیت‌های تعریف‌شده در دیتابیس فعلی، می‌توان از نمای سیستمی sys.objects استفاده کرد.

SELECT
    name AS ConstraintName,
    type_desc AS ConstraintType,
    create_date AS CreateDate,
    modify_date AS ModifyDate
FROM sys.objects
WHERE type IN ('PK', 'UQ', 'F', 'C', 'D')
ORDER BY type_desc, name;

در این اسکریپت، انواع مختلف Constraintها نمایش داده می‌شوند:

• PK: کلید اصلی یا Primary Key
• UQ: محدودیت یکتا یا Unique
• F: کلید خارجی یا Foreign Key
• C: محدودیت بررسی مقدار یا Check Constraint
• D: مقدار پیش‌فرض یا Default Constraint

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

بررسی Primary Key در تمام جداول

Primary Key مشخص می‌کند که هر رکورد در یک جدول چگونه به‌صورت یکتا شناسایی شود.
این محدودیت از ثبت کلیدهای تکراری و مقادیر NULL در ستون‌های کلید اصلی جلوگیری می‌کند.
اسکریپت زیر نام جدول، نام Constraint و ستون‌های تشکیل‌دهنده کلید اصلی را نمایش می‌دهد.

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    kc.name AS PrimaryKeyName,
    c.name AS ColumnName,
    ic.key_ordinal AS KeyPosition
FROM sys.key_constraints kc
INNER JOIN sys.tables t
    ON kc.parent_object_id = t.object_id
INNER JOIN sys.index_columns ic
    ON kc.parent_object_id = ic.object_id
    AND kc.unique_index_id = ic.index_id
INNER JOIN sys.columns c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
WHERE kc.type = 'PK'
ORDER BY
    SchemaName,
    TableName,
    KeyPosition;

ستون KeyPosition ترتیب ستون‌ها را در کلید اصلی نشان می‌دهد.
این موضوع در کلیدهای مرکب (Composite Primary Keys) اهمیت زیادی دارد؛ زیرا ممکن است یک کلید اصلی از چند ستون تشکیل شده باشد.

بررسی Foreign Keyها و ارتباط بین جداول

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

SELECT
    fk.name AS ForeignKeyName,
    SCHEMA_NAME(pt.schema_id) AS ParentSchema,
    pt.name AS ParentTable,
    pc.name AS ParentColumn,
    SCHEMA_NAME(rt.schema_id) AS ReferencedSchema,
    rt.name AS ReferencedTable,
    rc.name AS ReferencedColumn,
    fk.is_disabled AS IsDisabled,
    fk.is_not_trusted AS IsNotTrusted
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
    ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables pt
    ON fkc.parent_object_id = pt.object_id
INNER JOIN sys.columns pc
    ON fkc.parent_object_id = pc.object_id
    AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.tables rt
    ON fkc.referenced_object_id = rt.object_id
INNER JOIN sys.columns rc
    ON fkc.referenced_object_id = rc.object_id
    AND fkc.referenced_column_id = rc.column_id
ORDER BY ParentTable, ForeignKeyName;

دو ستون IsDisabled و IsNotTrusted اهمیت ویژه‌ای دارند.
اگر IsDisabled برابر یک باشد، Constraint غیرفعال است.
اگر IsNotTrusted برابر یک باشد، SQL Server نمی‌تواند تضمین کند که تمام داده‌های موجود با محدودیت تعریف‌شده مطابقت دارند.
در کلیدهای خارجی مرکب، این اسکریپت برای هر جفت ستون یک ردیف تولید می‌کند.

بررسی CHECK Constraintها

CHECK Constraint برای محدودکردن مقادیر مجاز یک ستون یا مجموعه‌ای از ستون‌ها به کار می‌رود.
برای مثال، می‌توان تعیین کرد که سن افراد مثبت باشد یا مبلغ سفارش کمتر از صفر نباشد.

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    cc.name AS CheckConstraintName,
    cc.definition AS CheckExpression,
    cc.is_disabled AS IsDisabled,
    cc.is_not_trusted AS IsNotTrusted
FROM sys.check_constraints cc
INNER JOIN sys.tables t
    ON cc.parent_object_id = t.object_id
ORDER BY
    SchemaName,
    TableName,
    CheckConstraintName;

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

([Price] >= (0))

این عبارت بیان می‌کند که مقدار ستون Price باید بزرگ‌تر یا مساوی صفر باشد.
اگر Constraint غیرفعال باشد یا وضعیت Trusted نداشته باشد، باید شرایط ایجاد آن و سابقه تغییرات جدول بررسی شود.

بررسی UNIQUE Constraintها

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

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    kc.name AS UniqueConstraintName,
    c.name AS ColumnName,
    ic.key_ordinal AS KeyPosition
FROM sys.key_constraints kc
INNER JOIN sys.tables t
    ON kc.parent_object_id = t.object_id
INNER JOIN sys.index_columns ic
    ON kc.parent_object_id = ic.object_id
    AND kc.unique_index_id = ic.index_id
INNER JOIN sys.columns c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
WHERE kc.type = 'UQ'
ORDER BY
    SchemaName,
    TableName,
    UniqueConstraintName,
    KeyPosition;

این دستور ستون‌های مربوط به هر UNIQUE Constraint را نمایش می‌دهد.
در صورت استفاده از کلید یکتا مرکب، چند ردیف برای آن مشاهده خواهید کرد.
توجه کنید که ایندکس یکتای مستقل نیز می‌تواند یکتایی داده‌ها را تضمین کند، اما الزاماً یک UNIQUE Constraint نیست؛ بنابراین، این کوئری تمام ایندکس‌های یکتا را فهرست نمی‌کند.

بررسی Default Constraintها

Default Constraint هنگام درج رکورد، در صورت مشخص‌نشدن مقدار ستون در دستور INSERT، مقدار پیش‌فرض تعیین‌شده را اعمال می‌کند.

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    dc.name AS DefaultConstraintName,
    dc.definition AS DefaultValue
FROM sys.default_constraints dc
INNER JOIN sys.tables t
    ON dc.parent_object_id = t.object_id
INNER JOIN sys.columns c
    ON dc.parent_object_id = c.object_id
    AND dc.parent_column_id = c.column_id
ORDER BY
    SchemaName,
    TableName,
    ColumnName;

برای مثال، ممکن است مقدار پیش‌فرض یک ستون به صورت ((0)) یا (()getdate) تعریف شده باشد.
عبارت دوم تاریخ و زمان جاری را هنگام درج رکورد فراهم می‌کند.

بررسی Constraintهای غیرفعال یا غیرقابل اعتماد

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

SELECT
    name AS ConstraintName,
    'FOREIGN KEY' AS ConstraintType,
    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    is_disabled AS IsDisabled,
    is_not_trusted AS IsNotTrusted
FROM sys.foreign_keys
WHERE is_disabled = 1
   OR is_not_trusted = 1
UNION ALL
SELECT
    name AS ConstraintName,
    'CHECK' AS ConstraintType,
    OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
    OBJECT_NAME(parent_object_id) AS TableName,
    is_disabled AS IsDisabled,
    is_not_trusted AS IsNotTrusted
FROM sys.check_constraints
WHERE is_disabled = 1
   OR is_not_trusted = 1
ORDER BY TableName, ConstraintType;

این کوئری فقط Foreign Keyها و CHECK Constraintها را که یکی از این وضعیت‌ها را دارند نمایش می‌دهد.
برای فعال‌سازی و اعتبارسنجی مجدد یک Constraint، می‌توان از دستور زیر استفاده کرد:

ALTER TABLE dbo.YourTable
WITH CHECK CHECK CONSTRAINT YourConstraintName;

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

بررسی Constraintهای یک جدول مشخص

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

SELECT
    o.name AS ConstraintName,
    o.type_desc AS ConstraintType,
    OBJECT_SCHEMA_NAME(o.parent_object_id) AS SchemaName,
    OBJECT_NAME(o.parent_object_id) AS TableName
FROM sys.objects o
WHERE o.parent_object_id = OBJECT_ID(N'dbo.YourTable')
  AND o.type IN ('PK', 'UQ', 'F', 'C', 'D')
ORDER BY o.type_desc, o.name;

عبارت dbo.YourTable را با نام واقعی جدول جایگزین کنید.
برای نمونه، اگر جدول شما dbo.Orders باشد، تمام Constraintهای پشتیبانی‌شده این کوئری برای همان جدول فهرست می‌شوند.