אילוצי UNIQUE ב-SQLite: עמודה בודדת, מפתחות מורכבים ו-NULL

UNIQUE פירושו "אסורות כפילויות"

אילוץ UNIQUE אומר ל-SQLite שערכים בעמודה (או בקבוצת עמודות) אסור שיחזרו על עצמם בין שורות. כך אומרים "שני משתמשים לא יכולים לחלוק אימייל" או "קוד מוצר מופיע לכל היותר פעם אחת".

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name  TEXT
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');
INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada Two');

SELECT * FROM users;

ההכנסה השלישית נכשלת עם UNIQUE constraint failed: users.email. SQLite בודק את האילוץ בכל כתיבה ודוחה כל דבר שהיה יוצר כפילות. שתי השורות הראשונות נשמרות. השלישית אף פעם לא נכנסת.

מאחורי הקלעים, UNIQUE ממומש כאינדקס ייחודי, אותו מבנה נתונים ש-SQLite משתמש בו לחיפושים מהירים, כך שהבדיקה זולה והעמודה מקבלת אינדקס אוטומטית.

תחביר ברמת העמודה מול ברמת הטבלה

אפשר לכתוב UNIQUE בשתי דרכים. בשורה לצד עמודה, או כפסוקית נפרדת בסוף הגדרת הטבלה:

-- Column-level: simple, one column.
CREATE TABLE products (
    sku   TEXT UNIQUE,
    name  TEXT
);

-- Table-level: same effect, different placement.
CREATE TABLE products2 (
    sku   TEXT,
    name  TEXT,
    UNIQUE(sku)
);

לעמודה בודדת, שתי הדרכים שקולות: בחרו במה שנקרא טוב יותר. הצורה ברמת הטבלה הופכת להכרחית ברגע שצריך ייחודיות על פני יותר מעמודה אחת.

UNIQUE מורכב: כמה עמודות יחד

לפעמים עמודה בודדת לא ייחודית בפני עצמה, אבל שילוב צריך להיות. משתמש יכול להירשם לקורסים רבים, ולקורס יכולים להיות משתמשים רבים, אבל אותו זוג (user_id, course_id) לא צריך להופיע פעמיים:

CREATE TABLE enrollments (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    UNIQUE(user_id, course_id)
);

INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);
INSERT INTO enrollments (user_id, course_id) VALUES (1, 101);  -- fine, different course
INSERT INTO enrollments (user_id, course_id) VALUES (2, 100);  -- fine, different user
INSERT INTO enrollments (user_id, course_id) VALUES (1, 100);  -- fails: pair already exists

SELECT * FROM enrollments;

האילוץ הוא על הזוג, לא על כל עמודה לבדה. משתמש 1 יכול להירשם לקורסים רבים, לקורס 100 יכולים להיות משתמשים רבים, אבל רק פעם אחת לכל שילוב.

זו התבנית הבסיסית לטבלאות קישור ביחסי רבים לרבים.

UNIQUE מול PRIMARY KEY

הם נשמעים דומים והם קשורים, אבל הם לא אותו דבר:

• לטבלה יש לכל היותר PRIMARY KEY אחד. יכולים להיות לה אילוצי UNIQUE רבים.
• PRIMARY KEY הוא הזהות של השורה: מה שמפתחות זרים מצביעים עליו, מה ש-rowid הוא כינוי שלו.
• UNIQUE הוא רק "הערך הזה (או השילוב הזה) לא חוזר".
• בטבלה רגילה, עמודת UNIQUE יכולה להכיל ערכי NULL. PRIMARY KEY לא יכול (עם חריג היסטורי אחד שנדלג עליו).

צורה נפוצה:

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,   -- internal identity, never changes
    email    TEXT NOT NULL UNIQUE,  -- user-facing identifier, must be unique
    username TEXT NOT NULL UNIQUE   -- another unique handle
);

id הוא מה ששאר מסד הנתונים מתייחס אליו. email ו-username ייחודיים כי האפליקציה דורשת זאת, לא כי הם הזהות. אם משתמש משנה את האימייל שלו, ה-id נשאר זהה: זו בדיוק הסיבה להפריד ביניהם.

המוזרות של NULL

זה מכשיל כמעט את כולם בפעם הראשונה. עמודת UNIQUE ב-SQLite מקבלת כמה ערכי NULL שתרצו:

CREATE TABLE invites (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES (NULL);
INSERT INTO invites (email) VALUES ('ada@example.com');
INSERT INTO invites (email) VALUES ('ada@example.com');  -- this one fails

SELECT * FROM invites;

שלושה NULL, אין בעיה. שני 'ada@example.com', זו התנגשות.

הסיבה: SQL מתייחס ל-NULL כ"לא ידוע", ושני ערכים לא ידועים לא נחשבים שווים, ולכן בדיקת הייחודיות לא יכולה לקבוע שהם כפולים. אם צריך לכל היותר NULL אחד, התיקון הנקי ביותר הוא NOT NULL UNIQUE. אם NULL הוא ערך תקין אבל רק אחד לכל שילוב של עמודה אחרת, פנו לאינדקס חלקי (מוסבר בהמשך, בפרק על אינדקסים).

טיפול בהתנגשויות: ON CONFLICT

כברירת מחדל, הפרה של UNIQUE מבטלת את הפקודה. אבל לפעמים רוצים התנהגות אחרת: להחליף את השורה הקיימת, להתעלם מהחדשה, או לעדכן עמודות מסוימות. SQLite נותן שתי דרכים לבקש את זה.

הראשונה מובנית באילוץ עם ON CONFLICT:

CREATE TABLE settings (
    key   TEXT UNIQUE ON CONFLICT REPLACE,
    value TEXT
);

INSERT INTO settings (key, value) VALUES ('theme', 'light');
INSERT INTO settings (key, value) VALUES ('theme', 'dark');   -- replaces the first
INSERT INTO settings (key, value) VALUES ('lang', 'en');

SELECT * FROM settings;

בפעם השנייה ש-theme מוכנס, השורה הקיימת נמחקת והחדשה תופסת את מקומה. אפשרויות נוספות הן IGNORE (דילוג שקט), ABORT (ברירת המחדל), FAIL ו-ROLLBACK.

הדרך השנייה היא ברמת הפקודה, עם תחביר ה-upsert, ובדרך כלל גמישה יותר כי היא יכולה לעדכן עמודות מסוימות:

CREATE TABLE counters (
    name  TEXT UNIQUE,
    count INTEGER NOT NULL DEFAULT 0
);

INSERT INTO counters (name, count) VALUES ('hits', 1);
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;
INSERT INTO counters (name, count) VALUES ('hits', 1)
    ON CONFLICT(name) DO UPDATE SET count = count + 1;

SELECT * FROM counters;

ההכנסה הראשונה יוצרת את השורה. שתי הבאות נתקלות באילוץ ה-UNIQUE ועוברות לענף ה-DO UPDATE, שמגדיל את count. זו תבנית ה-upsert של INSERT ... ON CONFLICT, ויש עליה דף ייעודי בהמשך.

אילוץ UNIQUE מול אינדקס UNIQUE

CREATE UNIQUE INDEX עושה את אותה עבודה כמו אילוץ UNIQUE. למעשה, אילוץ UNIQUE יוצר אינדקס ייחודי מאחורי הקלעים: זה כמעט אותו מנגנון עם כובעים שונים.

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

CREATE UNIQUE INDEX idx_users_email ON users(email);

INSERT INTO users (email) VALUES ('ada@example.com');
INSERT INTO users (email) VALUES ('ada@example.com');  -- fails

מתי להעדיף כל אחד:

• אילוץ כשהייחודיות היא חלק מהגדרת הטבלה. היא מתועדת ממש ליד העמודות.
• אינדקס ייחודי כשרוצים אינדקס חלקי (פסוקית WHERE), צריכים שם מסוים, או רוצים להוסיף אותו לטבלה קיימת בלי לכתוב אותה מחדש. ALTER TABLE של SQLite לא יכול להוסיף אילוץ, אבל תמיד אפשר להוסיף אינדקס.

ההתנהגות בכתיבה זהה. הבחירה היא בעיקר איפה אתם רוצים שהכלל יחיה בסכמה.

הוספת UNIQUE לטבלה קיימת

ALTER TABLE של SQLite מוגבל בכוונה: אין ALTER TABLE ... ADD CONSTRAINT. שתי האפשרויות המעשיות:

-- Option 1: add a unique index. Works on existing tables, same enforcement.
CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    email TEXT
);

INSERT INTO customers (email) VALUES ('ada@example.com');
INSERT INTO customers (email) VALUES ('boris@example.com');

CREATE UNIQUE INDEX idx_customers_email ON customers(email);

INSERT INTO customers (email) VALUES ('ada@example.com');  -- now fails

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

שימו לב: אם אתם מוסיפים ייחודיות לעמודה שכבר יש בה כפילויות, ה-CREATE UNIQUE INDEX ייכשל. נקו קודם את השורות הכפולות, ואז הוסיפו את האינדקס.

כש-UNIQUE נכשל: קריאת השגיאה

הודעת השגיאה אומרת בדיוק איזה אילוץ התפוצץ:

Error: UNIQUE constraint failed: users.email
Error: UNIQUE constraint failed: enrollments.user_id, enrollments.course_id

הצורה הראשונה היא אילוץ על עמודה בודדת, users.email. השנייה היא מורכבת: שתי העמודות מופיעות כי השילוב כבר קיים. כשאתם רואים את זה:

1. זהו איזו שורה כבר מכילה את הערך המתנגש (SELECT ... WHERE email = '...').
2. החליטו אם אתם רוצים לעדכן את השורה הזו, לדלג על ההכנסה או להשתמש בערך אחר.
3. אם כפילויות צפויות ואתם רוצים למזג אותן, עברו ל-INSERT ... ON CONFLICT DO UPDATE.

השגיאה רועשת כי ברוב המקרים אתם באמת רוצים לדעת: כפילויות שקטות היו גרועות יותר מכתיבה שנכשלה.

הבא בתור: מחיקה ושינוי של טבלאות

אי אפשר להוסיף אילוצי UNIQUE לטבלה קיימת עם ALTER TABLE פשוט. המגבלה הזו היא הסיבה של-SQLite יש ריקוד מיוחד לשינויי סכמה, כתיבת הטבלה מחדש, וזה הנושא של הדף הבא, לצד היסודות של מחיקת טבלאות בצורה נקייה.