اسکریپت لیست جداول فاقد Foreign Key

اسکریپت لیست جداول فاقد Foreign Key

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

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

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

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

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

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

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

Foreign Key چیست؟

Foreign Key ستونی یا مجموعه‌ای از ستون‌ها در یک جدول است که به یک Primary Key یا کلید یکتا در جدول دیگر اشاره می‌کند.
برای مثال فرض کنید دو جدول با نام‌های Customers و Orders داریم. جدول مشتریان می‌تواند اطلاعات مشتری را نگهداری کند و جدول سفارش‌ها اطلاعات سفارش‌های ثبت‌شده را.
ساختار ساده جدول مشتریان می‌تواند به شکل زیر باشد:

CREATE TABLE Customers
(
    CustomerId INT PRIMARY KEY,
    FullName NVARCHAR(100)
);

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

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    CustomerId INT,
    OrderDate DATE,
    CONSTRAINT FK_Orders_Customers
        FOREIGN KEY (CustomerId)
        REFERENCES Customers(CustomerId)
);

در این مثال، ستون CustomerId در جدول Orders یک Foreign Key است و به CustomerId در جدول Customers اشاره می‌کند.
به این ترتیب پایگاه داده متوجه می‌شود که سفارش باید به یک مشتری معتبر مربوط باشد.

چرا باید جداول فاقد Foreign Key را پیدا کنیم؟

در یک پروژه کوچک ممکن است بررسی دستی جداول ساده باشد، اما زمانی که یک دیتابیس دارای ده‌ها یا صدها جدول باشد، بررسی تک‌تک آن‌ها زمان‌بر خواهد بود.
فرض کنید یک دیتابیس شامل ۲۰۰ جدول است و می‌خواهید بدانید کدام جدول‌ها هیچ رابطه‌ای با سایر جدول‌ها ندارند.
استفاده از یک اسکریپت SQL می‌تواند این کار را در چند ثانیه انجام دهد.
این بررسی در موارد مختلف کاربرد دارد؛ برای مثال:

• بررسی کیفیت طراحی دیتابیس

• مستندسازی ساختار پایگاه داده

• پیدا کردن جداول مستقل

• بررسی دیتابیس‌های قدیمی

• تحلیل روابط بین جداول

• شناسایی جداولی که احتمالاً ارتباط آن‌ها به‌درستی تعریف نشده است

• آماده‌سازی دیتابیس برای بازطراحی یا بهینه‌سازی

البته باید توجه داشت که فاقد Foreign Key بودن یک جدول الزاماً یک خطا نیست.
ممکن است جدول مربوط به تنظیمات، لاگ، اطلاعات سیستمی یا داده‌های مستقل باشد و اصلاً نیازی به Foreign Key نداشته باشد.

اسکریپت پیدا کردن جداول فاقد Foreign Key

در SQL Server می‌توان از اطلاعات سیستمی دیتابیس برای بررسی Foreign Keyها استفاده کرد.
یک اسکریپت ساده برای پیدا کردن جدول‌هایی که هیچ Foreign Key تعریف‌شده‌ای ندارند به صورت زیر است:

SELECT
    t.name AS TableName
FROM sys.tables AS t
LEFT JOIN sys.foreign_keys AS fk
    ON t.object_id = fk.parent_object_id
WHERE fk.object_id IS NULL
ORDER BY t.name;

این کوئری نام جدول‌هایی را نمایش می‌دهد که در آن‌ها Foreign Key به عنوان رابطه خروجی از جدول تعریف نشده است.

توضیح بخش‌های اسکریپت

در قسمت اول:

SELECT
    t.name AS TableName

نام جدول انتخاب می‌شود.
سپس:

FROM sys.tables AS t

از نمای سیستمی sys.tables استفاده می‌کنیم. این نما اطلاعات جدول‌های موجود در دیتابیس فعلی را در اختیار قرار می‌دهد.
در مرحله بعد:

LEFT JOIN sys.foreign_keys AS fk
    ON t.object_id = fk.parent_object_id

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

WHERE fk.object_id IS NULL

به دلیل استفاده از LEFT JOIN، اگر جدولی هیچ Foreign Key نداشته باشد، اطلاعات مربوط به Foreign Key برای آن جدول مقدار NULL خواهد داشت.
بنابراین با این شرط فقط جدول‌های فاقد Foreign Key نمایش داده می‌شوند.

بررسی Foreign Keyهای ورودی و خروجی

یک نکته مهم در تحلیل دیتابیس این است که «فاقد Foreign Key بودن» می‌تواند دو مفهوم متفاوت داشته باشد.
ممکن است یک جدول خودش به جدول دیگری Foreign Key داشته باشد، اما جدول دیگری به آن اشاره کرده باشد.
برای مثال:

Customers
   ↑
   |
Orders

در این ساختار، Orders دارای Foreign Key به Customers است.
بنابراین اگر هدف ما پیدا کردن جدول‌هایی باشد که هیچ Foreign Key خروجی ندارند، اسکریپت قبلی مناسب است.
اما اگر بخواهیم جدول‌هایی را پیدا کنیم که نه Foreign Key خروجی دارند و نه جدول دیگری با Foreign Key به آن‌ها متصل است، باید روابط ورودی را نیز بررسی کنیم.
برای این منظور می‌توان از اسکریپت زیر استفاده کرد:

SELECT
    t.name AS TableName
FROM sys.tables AS t
WHERE NOT EXISTS
(
    SELECT 1
    FROM sys.foreign_keys AS fk
    WHERE fk.parent_object_id = t.object_id
)
AND NOT EXISTS
(
    SELECT 1
    FROM sys.foreign_keys AS fk
    WHERE fk.referenced_object_id = t.object_id
)
ORDER BY t.name;

این کوئری فقط جدول‌هایی را نمایش می‌دهد که در هیچ رابطه Foreign Key به عنوان جدول مبدأ یا مقصد حضور ندارند.

تفاوت دو اسکریپت

تفاوت این دو روش بسیار مهم است.

اسکریپت اول:

SELECT
    t.name AS TableName
FROM sys.tables AS t
LEFT JOIN sys.foreign_keys AS fk
    ON t.object_id = fk.parent_object_id
WHERE fk.object_id IS NULL;

جدول‌هایی را پیدا می‌کند که Foreign Key خروجی ندارند.

اما اسکریپت دوم:

SELECT
    t.name AS TableName
FROM sys.tables AS t
WHERE NOT EXISTS
(
    SELECT 1
    FROM sys.foreign_keys AS fk
    WHERE fk.parent_object_id = t.object_id
)
AND NOT EXISTS
(
    SELECT 1
    FROM sys.foreign_keys AS fk
    WHERE fk.referenced_object_id = t.object_id
);

جدول‌هایی را پیدا می‌کند که هیچ ارتباط Foreign Key با سایر جدول‌ها ندارند.
بنابراین قبل از استفاده از اسکریپت باید مشخص کنیم دقیقاً چه چیزی می‌خواهیم بررسی کنیم.

نمایش تعداد Foreign Key برای هر جدول

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

SELECT
    t.name AS TableName,
    COUNT(fk.object_id) AS ForeignKeyCount
FROM sys.tables AS t
LEFT JOIN sys.foreign_keys AS fk
    ON t.object_id = fk.parent_object_id
GROUP BY t.name
ORDER BY ForeignKeyCount, t.name;

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

TableName       ForeignKeyCount
-------------   ---------------
Logs            0
Settings        0
Customers       1
Orders          2
OrderDetails    2

با این روش می‌توان به سرعت متوجه شد کدام جدول‌ها رابطه خارجی ندارند و کدام جدول‌ها دارای چندین رابطه هستند.

آیا نبود Foreign Key مشکل است؟

خیر، همیشه این‌طور نیست.
برای مثال جدول زیر:

ApplicationSettings

ممکن است فقط تنظیمات عمومی برنامه را ذخیره کند و هیچ ارتباطی با جدول دیگری نداشته باشد. در چنین شرایطی نبود Foreign Key کاملاً طبیعی است.
همچنین جدول‌هایی مانند لاگ سیستم ممکن است به دلایل مختلف بدون Foreign Key طراحی شوند.
اما اگر مثلاً جدول OrderDetails دارای ستونی به نام OrderId باشد و قرار باشد هر رکورد آن متعلق به یک سفارش باشد، ولی هیچ Foreign Key برای این ارتباط تعریف نشده باشد، لازم است ساختار دیتابیس با دقت بیشتری بررسی شود.
در این حالت ممکن است برنامه از طریق کد، ارتباط را کنترل کند؛ اما خود دیتابیس الزاماً از صحت ارتباط مطمئن نمی‌شود.