المفاتيح الأجنبية في SQLite: REFERENCES و ON DELETE

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

المفتاح الأجنبي (foreign key) في sqlite هو ببساطة عمود في جدول لا تُقبل قيمته إلا إذا كانت موجودة فعلاً في صف بجدول آخر. هكذا تقول قواعد البيانات العلائقية إنّ "هذا الصف في جدول posts يخصّ ذاك الصف في جدول authors"، دون الحاجة إلى تكرار اسم الكاتب وبريده في كل تدوينة.

إليك أبسط مثال ممكن: جدول أب وجدول ابن يربط بينهما مفتاح أجنبي.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('آدا'), ('بوريس');
INSERT INTO posts (title, author_id) VALUES ('مرحبا', 1), ('ملاحظات', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) هو تعريف المفتاح الأجنبي بالكامل. معناه ببساطة: هذا العمود يحمل قيمة id مأخوذة من جدول authors. الآن أصبحت قاعدة البيانات على علم بأن الجدولين مرتبطان ببعضهما، وستقوم — إذا كان التحقق مفعّلاً — برفض أي إدخال يشير إلى مؤلف غير موجود.

المفاتيح الأجنبية في sqlite معطّلة افتراضياً

هذه أهم معلومة يجب أن تعرفها عن المفاتيح الأجنبية في sqlite، وهي تفاجئ الجميع: محرّك SQLite يقرأ جملة REFERENCES ويفهمها، لكنه لا يطبّقها فعلياً ما لم تطلب منه ذلك صراحةً. والسبب يعود إلى التوافق مع الإصدارات القديمة، إذ بُنيت قواعد بيانات كثيرة قبل أن تظهر هذه الميزة أصلاً.

شاهد ما يحدث حين يكون التحقق معطّلاً:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- المؤلف 999 غير موجود، لكن SQLite يقبل هذا بكل سرور:
INSERT INTO posts (title, author_id) VALUES ('يتيم', 999);

SELECT * FROM posts;

لقد أُدرج السطر اليتيم مباشرةً دون أي اعتراض. ولكي تحصل فعليًا على الحماية التي تريدها من قيود المفاتيح الأجنبية، نفّذ الأمر PRAGMA foreign_keys = ON; في بداية كل اتصال جديد بقاعدة البيانات:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('يتيم', 999);

الآن ستفشل عملية الإدراج وتظهر لك رسالة FOREIGN KEY constraint failed. السبب أنّ هذا الـ pragma مرتبط بالاتصال نفسه لا بالملف، فلا يُحفَظ داخل قاعدة البيانات. يعني كل تطبيق وكل جلسة CLI وكل test fixture يجب أن يضبطه من جديد. ولهذا السبب، معظم الأكواد في بيئة الإنتاج تنفّذ الأمر PRAGMA foreign_keys = ON; مباشرةً بعد فتح الاتصال.

ماذا تشترط جملة REFERENCES فعليًا؟

العمود الذي تُشير إليه يجب أن يكون PRIMARY KEY أو يحمل قيد UNIQUE، حتى يستطيع SQLite ضمان أنّ عملية البحث ستُرجع نتيجة واحدة لا لبس فيها. كذلك يُفضَّل أن تكون الأنواع متوافقة، فرغم أنّ SQLite متساهل في موضوع الأنواع، إلا أنّ خلطها يفتح الباب لمفاجآت غير مرغوبة.

يمكنك كتابة المفتاح الأجنبي بطريقتين. الأولى مدمجة داخل تعريف العمود نفسه:

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

أو يمكنك تعريفه كقيد مستقل على مستوى الجدول، وهذا ضروري عندما يمتد المفتاح الأجنبي عبر أكثر من عمود:

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

كلا الصيغتين تُنتجان نفس القيد تمامًا، فاستخدم ما يبدو أوضح وأكثر انسجامًا مع الجدول الذي بين يديك.

ON DELETE: ماذا يحدث للأبناء عند حذف الأب؟

عندما تحذف صفًا من الجدول الأب، يجب على SQLite أن يقرر ماذا يفعل بالصفوف الأبناء التي تشير إليه. أنت من يحدد هذه السياسة عبر ON DELETE:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id) ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

حذف Ada أدّى إلى حذف منشوريها معًا. الخيارات المتاحة هي:

• CASCADE — يحذف السجلات التابعة أيضًا. مناسب للبيانات "المملوكة" مثل المنشورات التابعة لكاتب أو العناصر التابعة لطلب شراء.
• SET NULL — يجعل قيمة عمود المفتاح الأجنبي NULL. مفيد عندما تريد بقاء السجلات التابعة بعد اختفاء الأب (مثلًا: التعليقات على مستخدم محذوف تتحول إلى مجهولة).
• SET DEFAULT — يضبط عمود المفتاح الأجنبي على القيمة الافتراضية المعرَّفة له.
• RESTRICT — يمنع الحذف إذا وُجد أي سجل تابع. يفشل فورًا عند تنفيذ العبارة.
• NO ACTION — السلوك الافتراضي. يشبه RESTRICT عمليًا في معظم الحالات (يؤجل الفحص إلى وقت الـ commit، لكن النتيجة واحدة: لا يُسمح بترك سجلات تابعة معلّقة).

أما ON UPDATE فيعمل بالطريقة نفسها عند تغيير قيمة مفتاح الأب، وإن كان تعديل المفاتيح الأساسية أمرًا نادرًا في الواقع.

حل خطأ foreign key constraint failed وفهم سببه

ستواجه هذا الخطأ في حالتين. الأولى: عند إدراج أو تحديث سجل تابع بقيمة لا يقابلها أي سجل في جدول الأب:

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

إمّا أنّ المؤلف صاحب المعرّف 999 غير موجود، أو أنّك خلطت بين أنواع الأعمدة. أدخِل السجل الأب أولاً، أو صحّح القيمة.

ثانياً: حذف (أو تحديث) سجل أب لا يزال مرتبطاً بسجلات أبناء، عندما يستخدم المفتاح الأجنبي RESTRICT أو NO ACTION:

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

إمّا أن تحذف السجلات الأبناء أولًا، أو أن تغيّر المفتاح الأجنبي إلى ON DELETE CASCADE أو SET NULL إذا كان السلوك التتابعي هو ما تريده فعلًا.

وهناك أيضًا خطأ أقل شيوعًا من نفس العائلة، وهو FOREIGN KEY mismatch. يظهر هذا الخطأ حين لا يكون العمود المُشار إليه مفتاحًا أساسيًا أو فريدًا، أو حين لا يتطابق عدد الأعمدة بين الطرفين. وهو خطأ في تصميم المخطط (schema)، لا في البيانات.

إضافة مفتاح أجنبي إلى جدول موجود مسبقًا

الأمر ALTER TABLE في SQLite محدود الإمكانيات؛ فبإمكانك إضافة عمود جديد يحتوي على مفتاح أجنبي، لكن لا يمكنك إضافة مفتاح أجنبي إلى عمود موجود سلفًا. والحل المعتاد هو ما يُعرف بحركة "إعادة التسمية وإعادة البناء":

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER
);

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('مرحبا', 1), ('العالم', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

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

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

النمط المتّبع: عطّل التحقق من المفاتيح الأجنبية، أنشئ الجدول الجديد بالقيود التي تريدها، انسخ البيانات، احذف الجدول القديم، ثم أعد التسمية. وجود BEGIN/COMMIT يضمن أن العملية ذرّية. بعد الانتهاء، أعد تفعيل التحقق وسيقوم SQLite بفحص جميع الصفوف الموجودة مقابل القيود الجديدة — لكن انتبه: إن كانت هناك بيانات غير صالحة، فالمعاملة تكون قد تمّت بالفعل، لذا تحقّق من البيانات مسبقاً إذا كان الأمر يقلقك.

شغّل PRAGMA foreign_key_check; بعد الترحيل للتأكد من عدم وجود صفوف يتيمة.

مثال واقعي على المخطط

لنجمع كل ما سبق في مخطط بسيط لمدوّنة، يحتوي على جداول أب وجداول ابن، إضافة إلى جدول ربط لعلاقة many-to-many مع الوسوم:

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE posts (
    id         INTEGER PRIMARY KEY,
    author_id  INTEGER NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    title      TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE tags (
    id   INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

ثلاث ملاحظات مهمّة هنا. أولاً، الحقل author_id معرَّف على أنه NOT NULL، يعني كل تدوينة يجب أن يكون لها مؤلف. ثانياً، علاقة posts → authors فيها CASCADE، فلو حذفت مؤلفاً، تروح كل تدويناته معه. ثالثاً، جدول الربط post_tags يطبّق CASCADE من الطرفين، فحذف تدوينة أو وسم يُنظِّف صفوف الربط تلقائياً.

عادات توفّر عليك وجع الرأس لاحقاً

• فعِّل PRAGMA foreign_keys = ON; مع كل اتصال جديد بقاعدة البيانات. اجعلها جزءاً من روتين فتح القاعدة في تطبيقك، لا أمراً تتذكّره أحياناً وتنساه أحياناً.
• أضف فهرساً (index) على عمود المفتاح الأجنبي. SQLite يفهرس مفتاح الجدول الأب تلقائياً، لكنه لا يفعل ذلك للجدول الابن، وON DELETE CASCADE يُجري بحثاً في الجدول الابن في كل مرة تُحذف فيها صفوف من الأب.
• اختر سلوك ON DELETE بوعي. القيمة الافتراضية (NO ACTION) آمنة، لكنها تعني أنك ستصطدم برسالة "constraint failed" في كل محاولة تنظيف. حدِّد ما الذي يجب أن يحدث وصرِّح به في تعريف الجدول.
• شغِّل PRAGMA foreign_key_check; بعد عمليات الترحيل (migrations) أو الاستيراد بالجملة، حتى تكتشف الصفوف اليتيمة قبل أن تتحوّل إلى أخطاء غامضة.

التالي: INNER JOIN

المفاتيح الأجنبية تصف العلاقة بين الجداول، أما عمليات الـ joins فهي الطريقة الفعلية للاستعلام عبرها. الصفحة التالية تشرح INNER JOIN: كيف تدمج صفوفاً من جداول مرتبطة وتختار الأعمدة التي تريدها من كل جدول.