الدرس 10 · الوحدة 5: تصميم الجداول والعلاقات

المفتاح الأجنبي وربط الجداول

العمود الذي يربط جدولًا بآخر ويحرس صحة الربط. ⏱ نحو 25 دقيقة.

بعد هذا الدرس ستستطيع أن:

تعرّف المفتاح الأجنبي والجدول الأب والابن.
تكتب المفتاح الأجنبي بطريقتيه.
تفسّر رفض الإدخال والحذف حفاظًا على التكامل المرجعي.
تختار بين RESTRICT و CASCADE و SET NULL.

١ما المفتاح الأجنبي؟

المفتاح الأجنبي Foreign Key: عمود في جدول يحمل قيمة المفتاح الأساسي لصف في جدول آخر. به تتحقق العلاقة التي رسمناها في المخطط.

staff.dept_id مفتاح أجنبي يشير إلى depts.id. الجدول الذي فيه المفتاح الأساسي (depts) يسمى الأب Parent، والذي فيه المفتاح الأجنبي (staff) يسمى الابن Child.

٢كتابته بطريقتين

قصيرة بجانب العمود، أو مفصّلة في سطر مستقل بعد الأعمدة (وهي أوضح، وتلزم حين يكون المفتاح من عمودين):

query.sql

-- 1) بجانب العمودdept_id INTEGER REFERENCES depts(id) -- 2) في سطر مستقلdept_id INTEGER,FOREIGN KEY (dept_id) REFERENCES depts(id)

query.sql

CREATE TABLE depts (  id   INTEGER PRIMARY KEY,  name TEXT NOT NULL);CREATE TABLE staff (  id      INTEGER PRIMARY KEY,  name    TEXT NOT NULL,  dept_id INTEGER REFERENCES depts(id));INSERT INTO depts VALUES (1, 'المبيعات'), (2, 'المستودع');INSERT INTO staff VALUES (10, 'سالم', 1), (11, 'منيرة', 1), (12, 'بدر', 2);SELECT * FROM staff;

النتيجة · 3 صفوف

id	name	dept_id
10	سالم	1
11	منيرة	1
12	بدر	2

لاحظ الترتيب: أنشأنا جدول الأب depts أولًا، ثم الابن. ولا يمكن أن يشير المفتاح الأجنبي إلى جدول لم يُنشأ بعد.

٣حارس التكامل المرجعي

المفتاح الأجنبي يرفض أمرين:

1) ابن يشير إلى أب غير موجود:

query.sql

CREATE TABLE depts (  id   INTEGER PRIMARY KEY,  name TEXT NOT NULL);CREATE TABLE staff (  id      INTEGER PRIMARY KEY,  name    TEXT NOT NULL,  dept_id INTEGER REFERENCES depts(id));INSERT INTO depts VALUES (1, 'المبيعات'), (2, 'المستودع');INSERT INTO staff VALUES (10, 'سالم', 1), (11, 'منيرة', 1), (12, 'بدر', 2);INSERT INTO staff VALUES (13, 'نوف', 7);

✓ أُضيف 3 صفوف

✗ خطأ (السطر 12): مخالفة المفتاح الأجنبي: القيمة غير موجودة في الجدول الأصل، أو أن صفوفًا أخرى تعتمد على هذا الصف

لا يوجد قسم رقمه 7.

2) حذف أب له أبناء:

query.sql

CREATE TABLE depts (  id   INTEGER PRIMARY KEY,  name TEXT NOT NULL);CREATE TABLE staff (  id      INTEGER PRIMARY KEY,  name    TEXT NOT NULL,  dept_id INTEGER REFERENCES depts(id));INSERT INTO depts VALUES (1, 'المبيعات'), (2, 'المستودع');INSERT INTO staff VALUES (10, 'سالم', 1), (11, 'منيرة', 1), (12, 'بدر', 2);DELETE FROM depts WHERE id = 1;

✓ أُضيف 3 صفوف

قسم المبيعات فيه سالم ومنيرة. لو حُذف لبقيا يشيران إلى قسم لا وجود له؛ ويسمى هذا «سجلًّا يتيمًا» Orphan.

٤ماذا يحدث للأبناء عند حذف الأب؟

بدل الرفض، يمكن أن نحدد ماذا يحدث للأبناء، بإضافة ON DELETE إلى المفتاح الأجنبي:

الخيار	عند حذف الأب	مثال مناسب
`RESTRICT` (أو بلا خيار)	يرفض الحذف.	لا نحذف عميلًا له فواتير.
`CASCADE`	يحذف الأبناء معه.	حذف الطلب يحذف أصنافه.
`SET NULL`	يجعل المفتاح الأجنبي في الأبناء NULL.	حذف القسم يترك الموظف بلا قسم.

query.sql

CREATE TABLE depts (  id   INTEGER PRIMARY KEY,  name TEXT NOT NULL);CREATE TABLE staff (  id      INTEGER PRIMARY KEY,  name    TEXT NOT NULL,  dept_id INTEGER REFERENCES depts(id) ON DELETE SET NULL);INSERT INTO depts VALUES (1, 'المبيعات'), (2, 'المستودع');INSERT INTO staff VALUES (10, 'سالم', 1), (11, 'منيرة', 1), (12, 'بدر', 2);DELETE FROM depts WHERE id = 1;SELECT * FROM staff;

النتيجة · 3 صفوف

id	name	dept_id
10	سالم	NULL
11	منيرة	NULL
12	بدر	2

query.sql

CREATE TABLE invoices (id INTEGER PRIMARY KEY, total REAL);CREATE TABLE invoice_lines (  invoice_id INTEGER REFERENCES invoices(id) ON DELETE CASCADE,  item       TEXT);INSERT INTO invoices VALUES (1, 50), (2, 30);INSERT INTO invoice_lines VALUES (1, 'قهوة'), (1, 'كيك'), (2, 'شاي'); DELETE FROM invoices WHERE id = 1;SELECT * FROM invoice_lines;

النتيجة · صف واحد

invoice_id	item
2	شاي

حذفنا الفاتورة 1، فحُذف سطراها تلقائيًّا، وبقي سطر الفاتورة 2.

٥المفتاح الأجنبي في متجر الواحة

في «متجر الواحة» ثلاثة مفاتيح أجنبية. اضغط 🗂 الجداول في المحرر لتراها، أو جرّب أن تحذف منتجًا بيع من قبل:

query.sql

DELETE FROM products WHERE id = 1;

✗ خطأ (السطر 1): مخالفة المفتاح الأجنبي: القيمة غير موجودة في الجدول الأصل، أو أن صفوفًا أخرى تعتمد على هذا الصف

query.sql

DELETE FROM products WHERE id = 4;

✓ حُذف صف واحد

التمر السكري (رقم 4) لم يُطلب قط، فلا أبناء له، فحُذف.

توقّع وتحقّق

المفتاح الأجنبي يشير إلى:

يربط الابن بأبيه.

حذف الطلب يحذف أصنافه معه. الخيار:

حذف متسلسل.

الصف الذي يشير إلى أب محذوف يسمى:

Orphan.

أي الجدولين يُنشأ أولًا؟

الابن يشير إلى الأب.

الخلاصة

المفتاح الأجنبي عمود في الابن يحمل المفتاح الأساسي للأب.
REFERENCES أب(عمود)، أو FOREIGN KEY (عمود) REFERENCES أب(عمود).
يرفض ابنًا لأب غير موجود، ويرفض حذف أب له أبناء.
ON DELETE CASCADE يحذف الأبناء، و SET NULL يتركهم بلا أب.

تحدٍّ برمجي: المؤلفون وكتبهم

متوسط

أنشئ جدولين مترابطين: المؤلفين وكتبهم. إذا حُذف المؤلف تُحذف كتبه معه.

المطلوب:

authors: العمودان id (مفتاح أساسي) و name (إلزامي).
books: الأعمدة id (مفتاح أساسي) و title (إلزامي) و author_id.
author_id إلزامي، ومفتاح أجنبي يشير إلى المؤلفين، مع الحذف المتسلسل.

👀 اعرض النتيجة المطلوبة

بعد التنفيذ: DELETE FROM authors WHERE id = 1; SELECT COUNT(*) FROM books

COUNT(*)
0

✓ التحقق الآلي من حلّك:

الكود يعمل بلا أخطاء
يقبل مؤلفًا
يقبل كتابًا لمؤلف موجود
يرفض كتابًا لمؤلف غير موجود
يرفض كتابًا بلا مؤلف
حذف المؤلف يحذف كتبه

💡 تلميح 1

أنشئ authors أولًا.

💡 تلميح 2

FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE

حاول بنفسك أولًا؛ للمسألة أكثر من حلّ صحيح.

solution.sql

CREATE TABLE authors (  id   INTEGER PRIMARY KEY,  name TEXT NOT NULL); CREATE TABLE books (  id        INTEGER PRIMARY KEY,  title     TEXT NOT NULL,  author_id INTEGER NOT NULL,  FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE);

انتقل إلى تقييم الدرساختيار من متعدد، وصح وخطأ، وأكمل الفراغ، وتتبّع الناتج.ابدأ التقييم ←

📝 معلم؟ أرسل ملاحظة للمؤلف عن هذا الدرس