اسکریپت لیست ستون‌های شرکت کننده در Foreign Key Constraint های یک دیتابیس

اسکریپت لیست ستون‌های شرکت‌کننده در Foreign Key Constraint های یک دیتابیس 

در پایگاه داده SQL Server، کلید خارجی (Foreign Key) یکی از مهم‌ترین ابزارها برای ایجاد ارتباط بین جدول‌ها و حفظ یکپارچگی داده‌ها است.
در پروژه‌های نرم‌افزاری بزرگ، ممکن است یک دیتابیس شامل ده‌ها یا صدها جدول باشد که از طریق محدودیت‌های کلید خارجی به یکدیگر متصل شده‌اند.
در چنین شرایطی، شناسایی ستون‌های شرکت‌کننده در این محدودیت‌ها می‌تواند به تحلیل ساختار دیتابیس، مستندسازی روابط و عیب‌یابی مشکلات کمک کند.
با استفاده از اسکریپت‌های SQL می‌توان اطلاعات مربوط به تمام Foreign Key Constraint های یک دیتابیس را استخراج کرد و نام جدول‌ها، ستون‌های مرتبط و جدول‌های مرجع را مشاهده کرد.

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

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

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

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

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

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

Foreign Key Constraint چیست؟

Foreign Key Constraint محدودیتی در SQL Server است که ارتباط بین ستون‌های یک جدول و ستون‌های مرجع در جدول دیگر را تعریف می‌کند. این محدودیت از ثبت برخی داده‌های نامعتبر جلوگیری می‌کند و به حفظ یکپارچگی ارجاعی (Referential Integrity) کمک می‌کند.
برای مثال، فرض کنید دو جدول با نام‌های Customers و Orders داریم.
جدول Customers اطلاعات مشتریان و جدول Orders اطلاعات سفارش‌های ثبت‌شده را نگهداری می‌کند.
اگر ستون CustomerID در جدول سفارش‌ها به ستون CustomerID در جدول مشتریان متصل باشد، یک محدودیت Foreign Key بین این دو جدول ایجاد می‌شود.
در این حالت، مقدار CustomerID در جدول Orders باید با یکی از مقادیر معتبر ستون مرجع مطابقت داشته باشد؛ البته ستون کلید خارجی می‌تواند در صورت اجازه تعریف آن، مقدار NULL نیز داشته باشد.

اسکریپت استخراج ستون‌های شرکت‌کننده در Foreign Key

برای استخراج اطلاعات کلیدهای خارجی در سطح یک دیتابیس، می‌توان از نماهای سیستمی SQL Server مانند sys.foreign_keys، sys.foreign_key_columns، sys.tables و sys.columns استفاده کرد.
اسکریپت زیر تمام ستون‌های شرکت‌کننده در محدودیت‌های Foreign Key دیتابیس فعلی را نمایش می‌دهد:

SELECT
    fk.name AS ForeignKeyName,
    ps.name AS ParentSchema,
    pt.name AS ParentTable,
    pc.name AS ParentColumn,
    rs.name AS ReferencedSchema,
    rt.name AS ReferencedTable,
    rc.name AS ReferencedColumn,
    fkc.constraint_column_id AS ColumnPosition,
    fk.delete_referential_action_desc AS OnDeleteAction,
    fk.update_referential_action_desc AS OnUpdateAction
FROM sys.foreign_keys AS fk
INNER JOIN sys.foreign_key_columns AS fkc
    ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables AS pt
    ON fkc.parent_object_id = pt.object_id
INNER JOIN sys.schemas AS ps
    ON pt.schema_id = ps.schema_id
INNER JOIN sys.columns AS pc
    ON fkc.parent_object_id = pc.object_id
    AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.tables AS rt
    ON fkc.referenced_object_id = rt.object_id
INNER JOIN sys.schemas AS rs
    ON rt.schema_id = rs.schema_id
INNER JOIN sys.columns AS rc
    ON fkc.referenced_object_id = rc.object_id
    AND fkc.referenced_column_id = rc.column_id
ORDER BY
    ParentSchema,
    ParentTable,
    ForeignKeyName,
    ColumnPosition;

این اسکریپت اطلاعات مربوط به محدودیت‌های کلید خارجی دیتابیس فعلی را استخراج می‌کند.
برای اجرای آن کافی است ابتدا دیتابیس موردنظر را در محیط SQL Server انتخاب کنید.

بررسی بخش‌های مختلف اسکریپت

۱. استفاده از sys.foreign_keys

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

۲. استفاده از sys.foreign_key_columns

مهم‌ترین بخش این اسکریپت، نمای sys.foreign_key_columns است.
این نما مشخص می‌کند هر Foreign Key از کدام ستون یا ستون‌ها تشکیل شده و به کدام ستون‌های جدول مرجع اشاره می‌کند.
ستون constraint_object_id شناسه محدودیت کلید خارجی را مشخص می‌کند.
با اتصال این شناسه به sys.foreign_keys می‌توان اطلاعات هر محدودیت را به ستون‌های مربوط به آن ارتباط داد.

۳. شناسایی جدول و ستون اصلی

در اسکریپت، عبارت‌های ParentTable و ParentColumn به جدول و ستون‌هایی اشاره دارند که محدودیت Foreign Key در آن‌ها تعریف شده است.
برای مثال، اگر جدول Orders دارای ستون CustomerID باشد که به جدول Customers اشاره کند، جدول Orders در بخش Parent و ستون CustomerID آن در بخش ParentColumn نمایش داده می‌شود.

۴. شناسایی جدول و ستون مرجع

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

۵. بررسی ترتیب ستون‌ها

ستون ColumnPosition از ویژگی constraint_column_id به دست می‌آید و ترتیب ستون‌ها را در یک محدودیت Foreign Key مشخص می‌کند.
این ویژگی به‌خصوص زمانی اهمیت دارد که کلید خارجی از چند ستون تشکیل شده باشد.
در چنین شرایطی، ترتیب ستون‌های کلید خارجی باید در کنار ستون‌های مرجع متناظر بررسی شود.

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

گاهی یک محدودیت Foreign Key به‌جای یک ستون، از دو یا چند ستون تشکیل می‌شود.
به این ساختار، کلید خارجی مرکب (Composite Foreign Key) گفته می‌شود.
برای مثال، ممکن است یک جدول جزئیات سفارش از ترکیب OrderID و ProductID برای ایجاد ارتباط استفاده کند.
در این حالت، اسکریپت هر ستون شرکت‌کننده در محدودیت را در یک ردیف جداگانه نمایش می‌دهد.
استفاده از ColumnPosition و مرتب‌سازی نتایج بر اساس آن کمک می‌کند ترتیب ستون‌های مرتبط به‌درستی مشخص شود.
بنابراین نباید تصور کرد هر ردیف خروجی الزاماً نشان‌دهنده یک Foreign Key مستقل است؛ چند ردیف ممکن است متعلق به یک محدودیت واحد باشند.

بررسی رفتار حذف و به‌روزرسانی

یکی دیگر از قابلیت‌های اسکریپت، نمایش رفتار تعریف‌شده برای عملیات حذف و به‌روزرسانی است.
ستون OnDeleteAction رفتار محدودیت هنگام حذف رکورد مرجع و ستون OnUpdateAction رفتار آن هنگام به‌روزرسانی مقدار مرجع را نشان می‌دهد.
مقادیر این ستون‌ها می‌توانند شامل NO_ACTION، CASCADE، SET_NULL و SET_DEFAULT باشند.
رفتار واقعی به تنظیمات محدودیت و شرایط داده‌ها بستگی دارد.
برای مثال، در حالت CASCADE حذف رکورد مرجع می‌تواند حذف رکوردهای وابسته را نیز به دنبال داشته باشد.

کاربردهای اسکریپت ستون‌های شرکت کننده در Foreign Key Constraint در مدیریت دیتابیس

این اسکریپت در پروژه‌های مختلف کاربرد دارد. هنگام مستندسازی پایگاه داده، می‌توان از خروجی آن برای تهیه فهرست ارتباط‌های بین جدول‌ها استفاده کرد.
در فرایند عیب‌یابی نیز شناسایی محدودیت‌های کلید خارجی کمک می‌کند علت خطاهای مربوط به ثبت یا حذف اطلاعات مشخص شود.
همچنین در زمان انتقال دیتابیس، بازطراحی ساختار جدول‌ها یا بررسی وابستگی‌ها، شناخت ستون‌های شرکت‌کننده در Foreign Key ضروری است.
این اطلاعات به توسعه‌دهندگان کمک می‌کند تأثیر تغییرات ساختاری را بهتر ارزیابی کنند.
باید توجه داشت که اسکریپت اطلاعات دیتابیس فعلی را استخراج می‌کند و مشاهده تمام فراداده‌ها به مجوزهای دسترسی کاربر وابسته است.
اگر کاربر به برخی اشیا دسترسی نداشته باشد، ممکن است نتایج کامل نباشند.