یادداشت‌ها

»

کلید خارجی (Foreign Key) ، محاسن و معایب آن

برای مدارس و دانش‌آموزان دو جدول زیر را درنظر بگیرید:

CREATE TABLE schools (id NOT NULL)
CREATE TABLE student (
    id NOT NULL,
    sch_id NOT NULL
)

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

برای جلوگیری از چنین مشکلی، می‌توان در زبان برنامه‌نویسی (مثلا PHP) ابتدا از عدم وجود دانش‌آموز اختصاص یافته به یک مدرسه اطمینان حاصل کرد و سپس اقدام به حذف آن مدرسه نمود. راه دوم برای جلوگیری از این مشکل، این است که از نام مدرسه در جدول دوم را یک کلید خارجی از جدول اول بنامیم. برای این کار، می‌توان از دستور CREATE TABLE یا ALTER TABLE استفاده کرد:

CREATE TABLE schools (id NOT NULL)
CREATE TABLE student (
    id NOT NULL,
    sch_id NOT NULL,
    FOREIGN KEY (sch_id) REFERENCES schools(id)
)

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

CREATE TABLE schools (id NOT NULL)
CREATE TABLE student (
    id NOT NULL,
    sch_id NOT NULL,
    FOREIGN KEY (sch_id) REFERENCES schools(id)
    ON DELETE CASCADE
)

همانطور که می‌بینید؛ کلید خارجی (Foreign key) فقط برای حفظ ثبات داده‌های مرتبط بهم، مفید هستند. کلیدهای خارجی موجب می‌شوند که برخی کوئری‌های UPDATE و DELETE که در جدول ابهام ایجاد می‌کند، اجرا نشود و جلوی آنها گرفته شود و الا کلیدهای خارجی نه تنها تاثیر مثبتی بر سرعت اجرا ندارند بلکه کمی از سرعت اجرای کوئری می‌کاهند.

یکی دیگر از محدودیت‌های استفاده از کلیدهای خارجی این است که هر دو جدول الزاما باید از یک نوع موتور ذخیره‌سازی (storage engine) استفاده کنند و Foreign Key و Referenced Key هر دو باید دقیقا دارای یک ساختار باشند و Referenced Key حتما باید index شده باشد.

---

یادداشت‌های مرتبط

1. روش پشتیبان گیری از پایگاه داده MySQL و طریقه بازگردانی آن
2. پایگاه داده MySQL و مدیریت آن
3. سرویس های آنلاین رایگان برای نوشتن، تست و بهینه سازی کدهای برنامه نویسی
4. همه چیز درباره داده ها و تصاویر Inline درون برنامه ای
5. نکاتی برای افزایش سرعت و کارآمدی سایت
6. حذف درخواست اضافه تصویر انیمیشن لودینگ
7. همه چیز درباره کش(cache) سمت سرور و کاربر
8. کار با داده های سلسله مراتبی در پایگاه داده
9. هنر css نویسی (نکاتی برای بهتر نوشتن استایل صفحات وب)