SQLite Foreign Keys: REFERENCES, ON DELETE ו-PRAGMA

מפתח זר הוא מצביע בין טבלאות

מפתח זר (foreign key) הוא עמודה בטבלה אחת שהערך שלה חייב להתאים לשורה בטבלה אחרת. כך מסדי נתונים רלציוניים אומרים "השורה הזו ב-posts שייכת לשורה ההיא ב-authors" בלי להעתיק את השם והאימייל של הכותב לכל פוסט.

הנה הדוגמה הקטנה ביותר האפשרית, טבלת אב וטבלת בן שמקושרות ב-FK:

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 ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Hello', 1), ('Notes', 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 מפענחת פסוקיות 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 ('Orphan', 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 ('Orphan', 999);

עכשיו ההכנסה נכשלת עם FOREIGN KEY constraint failed. ה-pragma הוא לכל חיבור, לא לכל מסד נתונים: ההגדרה לא נשמרת בקובץ. כל אפליקציה, כל סשן של CLI וכל fixture של בדיקות צריכים להגדיר אותו. רוב קוד הייצור מריץ PRAGMA foreign_keys = ON; מיד אחרי פתיחת חיבור.

מה פסוקית REFERENCES דורשת בפועל

העמודה שאליה מפנים חייבת להיות PRIMARY KEY או בעלת אילוץ UNIQUE. כך SQLite יכולה להבטיח שהחיפוש חד-משמעי. גם הטיפוסים צריכים להיות תואמים: SQLite גמישה לגבי טיפוסים, אבל ערבוב שלהם הוא הזמנה להפתעות.

אפשר לכתוב את ה-FK בשתי דרכים. בתוך הגדרת העמודה:

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: לאפס את עמודת ה-FK ל-NULL. מתאים כשהבנים צריכים לשרוד בלי אב (למשל תגובות של משתמש שנמחק הופכות לאנונימיות).
• SET DEFAULT: להגדיר את עמודת ה-FK לערך ברירת המחדל שהוצהר עבורה.
• 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 לא קיים, או שהתבלבלתם בטיפוסי העמודות. הכניסו קודם את האב, או תקנו את הערך.

השני: מחיקה (או עדכון) של אב שעדיין יש לו בנים, כשה-FK משתמש ב-RESTRICT או ב-NO ACTION:

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

או שתמחקו קודם את הבנים, או שתשנו את ה-FK ל-ON DELETE CASCADE/SET NULL אם מחיקה בשרשרת היא מה שאתם באמת רוצים.

יש גם בת דודה פחות נפוצה, FOREIGN KEY mismatch. היא מופיעה כשהעמודה שאליה מפנים איננה מפתח ראשי או ייחודית, או כשמספרי העמודות לא תואמים. זו שגיאת סכמה, לא שגיאת נתונים.

הוספת מפתחות זרים לטבלאות קיימות

ה-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 ('Hello', 1), ('World', 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; אחרי המיגרציה כדי לוודא שאין שורות יתומות.

סכמה מציאותית

נחבר הכול: סכמה קטנה של בלוג עם אבות, בנים וטבלת קישור לתגיות ביחס רבים-לרבים:

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: לכל פוסט חייב להיות כותב. ה-FK של posts → authors מוחק בשרשרת, כך שמחיקת כותב מוחקת את הפוסטים שלו. טבלת הקישור post_tags מוחקת בשרשרת משני הצדדים, כך שהסרה של פוסט או של תגית מנקה אוטומטית את שורות הקישור.

הרגלים שיחסכו לכם כאב ראש בהמשך

• הגדירו PRAGMA foreign_keys = ON; בכל חיבור. הפכו את זה לחלק משגרת פתיחת מסד הנתונים של האפליקציה, לא למשהו שצריך לזכור.
• הוסיפו אינדקס על עמודת ה-FK. SQLite מאנדקסת אוטומטית את המפתח של האב, אבל לא את זה של הבן, ו-ON DELETE CASCADE מבצע חיפוש בטבלת הבן בכל פעם שמוחקים אב.
• בחרו את ON DELETE בכוונה. ברירת המחדל (NO ACTION) בטוחה, אבל פירושה שתיתקלו ב-"constraint failed" בכל פעם שתנסו לנקות. החליטו מה צריך לקרות והצהירו על כך.
• הריצו PRAGMA foreign_key_check; אחרי מיגרציות או ייבוא המוני כדי לתפוס שורות יתומות לפני שהן הופכות לבאגים.

הצעד הבא: INNER JOIN

מפתחות זרים מתארים את הקשר; JOIN הוא הדרך לשלוף נתונים דרכו. העמוד הבא עוסק ב-INNER JOIN: שילוב שורות מטבלאות קשורות וקבלת העמודות שאתם רוצים מכל אחת.