خانه/ ارتباطات و JOINها/ کلید خارجی

کلید خارجی (Foreign Key)

مبتدی ۱۳ دقیقه مطالعه

در درس مقدمه گفتیم جدول‌های مختلف می‌توانند از طریق یک ستون مشترک به هم «مرتبط» شوند. کلید خارجی (Foreign Key) دقیقاً همان ستونی است که این ارتباط را برقرار می‌کند: ستونی در یک جدول که به کلید اصلی جدول دیگری اشاره می‌کند.

مثال: ارتباط دانش‌آموز و ثبت‌نام درس

فرض کن سه جدول داریم: students (دانش‌آموزان)، courses (درس‌ها)، و enrollments (ثبت‌نام‌ها) که رابطه‌ی بین آن دو را نگه می‌دارد:

sql

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FullName NVARCHAR(100)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    Title NVARCHAR(100)
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT FOREIGN KEY REFERENCES Students(StudentID),
    CourseID INT FOREIGN KEY REFERENCES Courses(CourseID)
);

این‌جا، Enrollments.StudentID یک کلید خارجی است که به Students.StudentID اشاره می‌کند. کلید خارجی به پایگاه‌داده می‌گوید: «مقدار این ستون، حتماً باید در جدول دیگر هم وجود داشته باشد».

کلید خارجی چه فایده‌ای دارد؟ (یکپارچگی ارجاعی)

بدون کلید خارجی، هیچ‌چیز جلوی ثبت یک enrollment با StudentIDی که اصلاً وجود ندارد را نمی‌گیرد — و این یعنی داده‌ی «یتیم» و بی‌معنا. کلید خارجی دقیقاً همین را جلوگیری می‌کند؛ به این تضمین، یکپارچگی ارجاعی (Referential Integrity) می‌گویند.

نکته‌ی مهم درباره‌ی Playground این سایت: SQL Server همیشه و به‌طور پیش‌فرض قیدهای کلید خارجی را اجرا می‌کند. اما SQLite (موتور این Playground) به‌طور پیش‌فرض این بررسی را خاموش نگه می‌دارد، مگر این‌که صراحتاً با دستور PRAGMA foreign_keys = ON; آن را روشن کنی. بیا هر دو حالت را با چشم خودت ببینی.

حالت پیش‌فرض: بدون فعال‌سازی، کلید خارجی رعایت نمی‌شود

بیا اول ببینیم بدون فعال‌سازی صریح، چه اتفاقی می‌افتد:

امتحانش کن — بدون PRAGMA (پیش‌فرض)

می‌بینی که درج موفق شد و یک «ثبت‌نامِ یتیم» با student_id = 999 (که اصلاً وجود ندارد) ساخته شد — دقیقاً همان مشکلی که کلید خارجی باید جلویش را بگیرد. در SQL Server واقعی این درج همیشه با خطا رد می‌شود؛ اما در SQLite، تا وقتی PRAGMA foreign_keys را روشن نکنی، این محافظت غیرفعال است.

فعال کردن اجرای کلید خارجی در SQLite

حالا همان پرس‌وجو را با یک خط اضافه امتحان کن:

امتحانش کن — با PRAGMA foreign_keys = ON

حالا خطای FOREIGN KEY constraint failed را می‌بینی — این همان رفتاری است که SQL Server همیشه و به‌طور پیش‌فرض دارد.

چه اتفاقی برای enrollments می‌افتد اگر یک دانش‌آموز حذف شود؟

وقتی یک ردیف در جدول والد (students) حذف می‌شود، اما ردیف‌های وابسته در جدول فرزند (enrollments) هنوز به آن اشاره می‌کنند، باید تصمیم بگیریم چه اتفاقی بیفتد. این تصمیم را با اقدامات ارجاعی (Referential Actions) مشخص می‌کنیم:

گزینه	رفتار
`NO ACTION` (پیش‌فرض)	اگر ردیف وابسته وجود داشته باشد، حذف والد اصلاً مجاز نیست
`CASCADE`	حذف والد، ردیف‌های وابسته را هم به‌طور خودکار حذف می‌کند
`SET NULL`	حذف والد، ستون کلید خارجی ردیف‌های وابسته را NULL می‌کند

sql

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT FOREIGN KEY REFERENCES Students(StudentID) ON DELETE CASCADE
);

احتیاط: ON DELETE CASCADE قدرتمند اما خطرناک است — حذف یک ردیف می‌تواند زنجیره‌ای از حذف‌های ناخواسته در جدول‌های دیگر ایجاد کند. در پروژه‌های واقعی، معمولاً فقط برای رابطه‌هایی که واقعاً معنای «جزء از کل» دارند (مثل حذف یک سفارش که آیتم‌های آن هم باید حذف شوند) استفاده می‌شود.

جمع‌بندی این درس

کلید خارجی، ستونی است که به کلید اصلی جدول دیگر اشاره می‌کند و یکپارچگی ارجاعی را تضمین می‌کند.
SQL Server همیشه کلید خارجی را اجرا می‌کند؛ SQLite نیازمند PRAGMA foreign_keys = ON; است.
ON DELETE CASCADE / SET NULL / NO ACTION مشخص می‌کنند حذف والد چه اثری روی فرزند دارد.

حالا که کلید اصلی و خارجی را می‌شناسی، وقتش رسیده وارد اصل موضوع این فصل شویم: JOIN — چطور داده‌ی چند جدول مرتبط را در یک پرس‌وجو با هم ترکیب کنیم.