📞 02166056460

محدودیت Foreign Key در sql

7 / 10
از 6 کاربر
بازگشت به لیست مقالات » | شنبه 2 آبان 1405 در ساعت 15 و 40 دقیقه | نظرات کاربران ( 0 )
آموزش SQL

کلید خارجی در SQL چیست؟ راهنمای کامل Foreign Key

تعریف، کاربرد، ساخت و حذف محدودیت کلید خارجی با مثال های کاربردی

کلید خارجی در SQL ابزاری برای ایجاد ارتباط معتبر بین جدول های پایگاه داده است. با استفاده از محدودیت FOREIGN KEY می توان اطمینان پیدا کرد که مقدارهای ثبت شده در جدول فرزند، به رکوردی واقعی در جدول مرجع اشاره می کنند. در این راهنما با مفهوم کلید خارجی، قواعد آن و نمونه دستورهای پرکاربرد آشنا می شوید.

۰۱

کلید خارجی در SQL چیست و چه کاربردی دارد؟

کلید خارجی یا Foreign Key ستونی در یک جدول است که به یک کلید یکتا در جدول دیگری اشاره می کند. در بسیاری از طراحی ها، این ستون به PRIMARY KEY جدول مرجع وصل می شود. به این ترتیب پایگاه داده می تواند ارتباط منطقی میان رکوردهای دو جدول را بررسی و از ذخیره بخشی از داده های ناسازگار جلوگیری کند.

برای نمونه، جدول Persons می تواند اطلاعات افراد را نگه دارد و جدول Orders سفارش های ثبت شده را ذخیره کند. اگر هر سفارش به یک شخص تعلق داشته باشد، ستون P_Id در جدول Orders می تواند به ستون P_Id در جدول Persons ارجاع دهد. در این مثال، Persons جدول والد یا مرجع و Orders جدول فرزند است.

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

🔗

جدول والد

جدولی که رکورد مرجع و کلید یکتای آن در آن نگهداری می شود.

📊

جدول فرزند

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

🛡️

یکپارچگی ارجاعی

قواعدی برای جلوگیری از ارتباط دادن رکوردها به داده های ناموجود.

۵ نکته مهم درباره کلید خارجی در SQL

۰۲

۱. کلید خارجی برای حفظ یکپارچگی داده ها است

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

فرض کنید در جدول Orders سفارشی برای شخصی با شناسه ۴۵ ثبت می کنید، اما چنین شناسه ای در Persons وجود ندارد. محدودیت کلید خارجی اجازه ثبت این سفارش را نمی دهد. این رفتار به ویژه زمانی مفید است که داده ها از چند فرم، برنامه یا سرویس مختلف وارد پایگاه داده می شوند.

۰۳

۲. مقدار کلید خارجی می تواند تکراری باشد

کلید خارجی الزام ندارد که در جدول فرزند یکتا باشد. برای مثال، ممکن است یک شخص چند سفارش داشته باشد؛ در این صورت P_Id همان شخص در چند ردیف از Orders تکرار می شود. این وضعیت با نقش کلید خارجی سازگار است، چون وظیفه آن شناسایی رکورد مرجع است، نه یکتا کردن ردیف های جدول فرزند.

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

۰۴

۳. حذف و تغییر رکورد والد به قواعد رابطه بستگی دارد

اگر رکوردی در جدول والد، رکوردهای وابسته در جدول فرزند داشته باشد، حذف یا تغییر کلید آن می تواند ارتباط موجود را خراب کند. رفتار پایگاه داده به تنظیمات محدودیت وابسته است. در حالت پیش فرض بسیاری از سیستم ها، حذف والد تا زمانی که رکورد فرزند وجود دارد پذیرفته نمی شود؛ با این کار از باقی ماندن سفارش هایی با شخص نامشخص جلوگیری می شود.

در بعضی رابطه ها، حذف رکوردهای فرزند همراه با حذف والد منطقی است؛ در رابطه های دیگر، حفظ سابقه اهمیت بیشتری دارد. برای مثال، حذف یک مشتری ممکن است نباید سابقه سفارش های مالی او را پاک کند. پس پیش از انتخاب رفتار حذف، پیامد آن را برای گزارش ها، سوابق و نیازهای کسب و کار بررسی کنید.

۰۵

۴. ستون های مرتبط باید از نظر نوع داده سازگار باشند

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

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

۰۶

۵. نام گذاری محدودیت ها عیب یابی را آسان می کند

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

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

مزایای کلید خارجی

داده های مرتبط و قابل اعتماد

کنترل رابطه ها در سطح پایگاه داده
۰۱
اعتبار داده

جلوگیری از ارجاع نامعتبر

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

۰۲
سازمان دهی

تفکیک اطلاعات مرتبط

هر جدول مسئول نگهداری نوعی از داده است.

۰۳
نگهداری

کاهش ناسازگاری

قواعد رابطه در خود پایگاه داده ثبت می شود.

۰۴
بررسی

آسان تر شدن عیب یابی

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

۰۵
مدل داده

نمایش رابطه جدول ها

ارتباط بخش های مختلف داده شفاف تر می شود.

۰۷

ساخت کلید خارجی هنگام ایجاد جدول

می توانید محدودیت کلید خارجی را هنگام ساخت جدول فرزند تعریف کنید. نمونه زیر با استفاده از دستور CREATE TABLE جدولی برای سفارش ها ایجاد می کند و P_Id را به ستون متناظر در جدول Persons مرتبط می سازد. این نمونه از شکل رایج دستور در SQL Server استفاده می کند.

CREATE TABLE Orders
(
    O_Id int NOT NULL PRIMARY KEY,
    OrderNo int NOT NULL,
    P_Id int,
    FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

در این ساختار، O_Id کلید اصلی جدول Orders است و P_Id نقش کلید خارجی را دارد. بنابراین چند ردیف سفارش می توانند به یک شخص اشاره کنند، اما مقدار P_Id غیر NULL باید در ستون مرجع Persons وجود داشته باشد. پیش از اجرای دستور، مطمئن شوید جدول Persons و ستون P_Id از قبل ساخته شده اند و ستون مرجع کلید اصلی یا دارای محدودیت یکتا است.

اگر قصد دارید نام محدودیت را خودتان انتخاب کنید، از عبارت CONSTRAINT استفاده کنید. این کار برای پروژه هایی که چندین رابطه دارند مفید است، چون نام محدودیت در پیام خطا و دستورهای تغییر ساختار قابل شناسایی خواهد بود.

CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    CONSTRAINT PK_Orders PRIMARY KEY (O_Id),
    CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
        REFERENCES Persons(P_Id)
);

قواعد حذف و به روزرسانی رکوردهای مرتبط

۰۸

انتخاب رفتار مناسب برای حذف یا تغییر

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

در مثال زیر، ON DELETE CASCADE باعث می شود حذف یک شخص، سفارش های وابسته او را نیز حذف کند. این انتخاب فقط زمانی مناسب است که حذف والد باید به طور قطعی حذف فرزندها را هم در پی داشته باشد. در سیستم های مالی یا سامانه هایی که سابقه سفارش اهمیت دارد، این رفتار می تواند ناخواسته باشد.

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
ON DELETE CASCADE
ON UPDATE CASCADE;

گزینه هایی مانند CASCADE، SET NULL و SET DEFAULT را بدون بررسی اثرشان در داده های واقعی انتخاب نکنید. برای نمونه، SET NULL تنها زمانی کاربرد دارد که ستون فرزند اجازه NULL داشته باشد. اگر هدف حفظ سوابق است، ممکن است محدود کردن حذف والد یا استفاده از وضعیت غیرفعال به جای حذف فیزیکی، انتخاب مناسب تری باشد.

یکپارچگی داده

ساختار پایگاه داده را با نیاز پروژه هماهنگ کنید

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

مشاهده خدمات طراحی سایت ←

افزودن کلید خارجی پس از ساخته شدن جدول

۰۹

افزودن محدودیت به یک ستون

اگر جدول Orders از قبل وجود دارد، می توانید با دستور ALTER TABLE یک محدودیت جدید اضافه کنید. در نمونه زیر ستون P_Id به ستون P_Id جدول Persons مرتبط می شود. نام گذاری محدودیت از همان ابتدا باعث می شود مدیریت و حذف آن در مراحل بعد ساده تر باشد.

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id);

قبل از اجرای دستور، داده های فعلی جدول فرزند را بررسی کنید. اگر در Orders مقداری وجود داشته باشد که در Persons ثبت نشده باشد، سامانه ممکن است از ایجاد محدودیت خودداری کند. در این شرایط ابتدا رکوردهای ناسازگار را اصلاح کنید، مقدار مرجع معتبر وارد کنید یا درباره مجاز بودن مقدار NULL تصمیم بگیرید.

۱۰

تعریف کلید خارجی روی چند ستون

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

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

CREATE TABLE ChildRecords
(
    ParentA int NOT NULL,
    ParentB int NOT NULL,
    CONSTRAINT fk_Child_Parent
        FOREIGN KEY (ParentA, ParentB)
        REFERENCES ParentRecords (ParentA, ParentB)
);

اشتباهات رایج هنگام استفاده از Foreign Key

۱۱

بررسی خطاهای متداول پیش از اجرا

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

🚨

رکورد والد ناموجود

مقدار جدول فرزند باید در ستون مرجع جدول والد وجود داشته باشد.

⚙️

نوع داده ناسازگار

تعریف دو ستون مرتبط را از نظر نوع و ویژگی ها بررسی کنید.

🔒

حذف ناخواسته فرزند

اثر ON DELETE CASCADE را پیش از استفاده ارزیابی کنید.

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

حذف محدودیت کلید خارجی

۱۲

حذف محدودیت با نام مشخص

برای حذف یک محدودیت Foreign Key، ابتدا نام دقیق آن را پیدا کنید. در SQL Server می توانید از دستور ALTER TABLE همراه با DROP CONSTRAINT استفاده کنید. حذف محدودیت، رابطه را از تعریف پایگاه داده برمی دارد؛ بنابراین پس از آن ممکن است داده های نامعتبر در جدول فرزند وارد شوند.

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders;

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

۱۳

راهنمای سریع انتخاب و استفاده از کلید خارجی

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

  • جدول والد را مشخص کنید: ستون مرجع باید کلید اصلی یا کلیدی یکتا و معتبر باشد.
  • جدول فرزند را تعیین کنید: ستونی را انتخاب کنید که ارتباط را در هر رکورد نگه می دارد.
  • نوع داده ها را تطبیق دهید: ستون های مرتبط باید برای تعریف رابطه سازگار باشند.
  • رفتار حذف را آگاهانه انتخاب کنید: از حذف زنجیره ای فقط در صورت نیاز واقعی استفاده کنید.
  • داده های موجود را بررسی کنید: مقدارهای ناسازگار مانع افزودن محدودیت می شوند.
  • برای محدودیت نام روشن بگذارید: نام گویا، تغییر ساختار و عیب یابی را آسان تر می کند.

نتیجه گیری

۱۴

جمع بندی کاربرد Foreign Key

کلید خارجی در SQL راهی مطمئن برای تعریف رابطه میان جدول ها و حفظ یکپارچگی ارجاعی است. با تعیین درست جدول والد، ستون مرجع، نام محدودیت و رفتار حذف یا به روزرسانی، می توانید از ثبت ارتباط های نامعتبر جلوگیری کنید و ساختار داده را برای توسعه و نگهداری آماده نگه دارید.

پیش از افزودن یا حذف محدودیت، داده های موجود و منطق برنامه را بررسی کنید. یک Foreign Key زمانی بهترین نتیجه را می دهد که با نیاز واقعی رابطه هماهنگ باشد و رفتارهای آن با آزمون های مناسب سنجیده شود.

رابطه های پایگاه داده را آگاهانه طراحی کنید

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

اطلاعات بیشتر ←

سوالات متداول کلید خارجی در SQL

کلید خارجی در SQL چه کاری انجام می دهد؟

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

آیا مقدار کلید خارجی می تواند تکراری باشد؟

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

آیا کلید خارجی می تواند مقدار NULL داشته باشد؟

بله، اگر ستون کلید خارجی اجازه NULL داشته باشد، ممکن است برخی رکوردها هنوز به رکورد والد مشخصی مرتبط نباشند. اگر ارتباط برای هر رکورد اجباری است، ستون را NOT NULL تعریف کنید. امکان استفاده از NULL به نوع داده و قواعد پایگاه داده وابسته است.

چرا هنگام افزودن Foreign Key خطا دریافت می کنم؟

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

چگونه یک محدودیت کلید خارجی را حذف کنم؟

در SQL Server می توانید از دستور ALTER TABLE نام جدول فرزند و سپس DROP CONSTRAINT به همراه نام محدودیت استفاده کنید. برای نمونه، دستور ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders محدودیت نام برده شده را حذف می کند. نام دقیق محدودیت را پیش از اجرا بررسی کنید.

ارسال نظر و نظرات ثبت شده
سایر مقالات
آموزش View در sql آموزش view در sql آموزش پایگاه داده ساخت، استفاده و مدیریت جدول‌های مجازی در SQL ...
فیلد identity در Sql فیلد identity در SQL چیست و چگونه از آن استفاده کنیم؟ ⭐ فیلد identity در SQL فیلد identity د ...
محدودیت Unique در sql SQL UNIQUE Constraint برای جلوگیری از ثبت مقدارهای تکراری در ستون‌های جدول به کار می‌رود. ...
محدودیت NOT NULL در sql آموزش پایگاه داده محدودیت NOT NULL در SQL چیست؟ آموزش کاربردی و مثال با تعریف این محدودیت، ...
محدودیت Check در sql آموزش پایگاه داده محدودیت CHECK در SQL؛ راهنمای کامل تعریف و مدیریت اعتبارسنجی داده‌ه ...
محدودیت Default در اسکیوال آموزش SQL محدودیت SQL DEFAULT؛ مقدار پیش فرض در SQL نحوه تعریف، تغییر و حذف مقدار پیش فرض ...
محدودیت Foreign Key در sql آموزش SQL کلید خارجی در SQL چیست؟ راهنمای کامل Foreign Key تعریف، کاربرد، ساخت و حذف محدود ...
محدودیت Primary Key در sql آموزش پایگاه داده محدودیت Primary Key در SQL؛ تعریف و کاربرد کلید اصلی راهنمای تعریف، استف ...
ایجاد محدودیت برای فیلد ها (Constraints) آموزش پایگاه داده محدودیت های SQL چیست و چگونه از آنها استفاده کنیم؟ راهنمای کاربردی آشنای ...
دستور alter table در sql آموزش پایگاه داده دستور ALTER TABLE در SQL؛ راهنمای افزودن، حذف و تغییر ستون آشنایی با ساخ ...
تغییر نام فیلد در یک جدول با استفاده از دستور sql آموزش SQL Server تغییر نام ستون در SQL Server با دستور sp_rename نحوه تغییر نام فیلد، مثال ...
دستور Create Index در sql آموزش SQL دستور CREATE INDEX در SQL چیست؟ آموزش ساخت اندیس، انتخاب ستون مناسب و بهبود سرعت ...
دستور Drop در sql دستور DROP در SQL؛ آموزش حذف جدول، اندیس و پایگاه داده 💡آشنایی با حذف ساختارها در SQL دستور ...
دستور Create Table در sql دستور CREATE TABLE در SQL؛ راهنمای ایجاد جدول در پایگاه داده 💡آشنایی با ساخت جدول دستور CRE ...
دستور CREATE DATABASE در sql دستور CREATE DATABASE در SQL چیست؟ 💡آشنایی با ساخت پایگاه داده برای ایجاد یک پایگاه داده ...
دسته بندی
جستجو
⏳ در حال پردازش...
لطفا صبر کنید