SQLite NOT NULL ו-DEFAULT: אילוצי עמודה שמחזיקים

שני אילוצים שמצדיקים את קיומם

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

CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    email    TEXT    NOT NULL,
    name     TEXT    NOT NULL DEFAULT 'anonymous',
    joined   TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

INSERT INTO users (email) VALUES ('rosa@example.com');

SELECT * FROM users;

עמודה אחת היא חובה ואין לה ערך חלופי. לשתיים יש ערכים חלופיים. ההכנסה הייתה צריכה לספק רק את email, ו-SQLite מילאה את השאר. זו כל התכונה בדוגמה אחת; שאר העמוד עוסק במקרי הקצה.

NOT NULL פירושו "לדחות NULL, בלי יוצאים מן הכלל"

NOT NULL עושה בדיוק מה שהוא אומר. כל ניסיון להכניס NULL לעמודה, בין אם על ידי השמטה שלה מ-INSERT כשאין ברירת מחדל ובין אם על ידי כתיבה מפורשת של NULL, נכשל:

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

-- זה נכשל: title חסר.
INSERT INTO posts (id) VALUES (1);

השגיאה נראית כך:

Runtime error: NOT NULL constraint failed: posts.title

אותה תוצאה אם מעבירים NULL ישירות:

INSERT INTO posts (id, title) VALUES (1, NULL);
-- שגיאה בזמן ריצה: NOT NULL constraint failed: posts.title

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

DEFAULT מספק ערך כשמי שקורא לא מספק

DEFAULT נכנס לפעולה רק כש-INSERT לא מזכיר את העמודה בכלל. הוא לא מציל NULL מפורש:

CREATE TABLE tasks (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO tasks (title) VALUES ('Write docs');
INSERT INTO tasks (title, status) VALUES ('Ship it', 'done');

SELECT * FROM tasks;

ההכנסה הראשונה מסתמכת על ברירת המחדל. השנייה דורסת אותה. אם הייתם כותבים INSERT INTO tasks (title, status) VALUES ('x', NULL), הייתם מקבלים שגיאת NOT NULL constraint failed: העמודה צוינה בשם, ולכן ברירת המחדל לא הופעלה אף פעם.

זה המודל המנטלי שכדאי להחזיק: DEFAULT ממלא את מקומן של עמודות חסרות. NOT NULL דוחה ערכי null בכל דרך שבה הם מגיעים. אלה תכונות עצמאיות שמשתלבות היטב.

ברירות מחדל יכולות להיות ביטויים

ברירת מחדל שהיא ליטרל היא המקרה הנפוץ (DEFAULT 0, DEFAULT '', DEFAULT 'pending'), אבל SQLite מקבלת גם ביטוי בסוגריים. כך מחתימים שורות בזמן היצירה שלהן, או מייצרים מזהה אקראי:

CREATE TABLE events (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    occurred   TEXT NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    token      TEXT NOT NULL DEFAULT (lower(hex(randomblob(8))))
);

INSERT INTO events (name) VALUES ('login');
INSERT INTO events (name) VALUES ('logout');

SELECT * FROM events;

כמה דברים שכדאי לדעת:

• הביטוי מחושב בכל הכנסה, לא פעם אחת בזמן יצירת הטבלה. כל שורה מקבלת חותמת זמן משלה וטוקן משלה.
• CURRENT_TIMESTAMP, CURRENT_DATE ו-CURRENT_TIME הן שלוש מילות המפתח המיוחדות שלא צריכות סוגריים. כל השאר צריך.
• הביטוי לא יכול להפנות לעמודות אחרות או לתת-שאילתות: הוא חייב לעמוד בפני עצמו.

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

DEFAULT NULL חוקי (ולפעמים זה כל העניין)

כתיבת DEFAULT NULL זהה להיעדר ברירת מחדל: העמודה היא NULL כשלא מספקים ערך. כדאי להשתמש בזה כשרוצים לומר במפורש בסכמה ש"אין ערך" הוא מצב ההתחלה המכוון:

CREATE TABLE profiles (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    bio       TEXT DEFAULT NULL,
    avatar    TEXT
);

INSERT INTO profiles (user_id) VALUES (1);

SELECT * FROM profiles;

bio ו-avatar מתנהגות כאן באופן זהה. ה-DEFAULT NULL על bio הוא הערה בצורת קוד: הוא אומר לכל מי שקורא את הסכמה שהיעדר ביוגרפיה הוא מצב רגיל, לא השמטה.

הוספת NOT NULL לטבלה קיימת

כאן העניינים נעשים מסובכים. ה-ALTER TABLE של SQLite מוגבל בכוונה: אי אפשר להריץ ALTER COLUMN ... SET NOT NULL כמו שאולי הייתם עושים ב-Postgres. מה שאפשר כן לעשות תלוי בשאלה אם העמודה כבר קיימת.

לעמודה חדשה לגמרי, ADD COLUMN ... NOT NULL עובד, אבל חובה לספק ברירת מחדל, אחרת בשורות הקיימות היה פתאום NULL בעמודת NOT NULL, וזה בלתי אפשרי:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products (name) VALUES ('widget'), ('gizmo');

-- עובד: השורות הקיימות מקבלות את ברירת המחדל.
ALTER TABLE products ADD COLUMN price REAL NOT NULL DEFAULT 0;

SELECT * FROM products;

נסו את אותו דבר בלי ברירת מחדל ותקבלו שגיאה:

ALTER TABLE products ADD COLUMN sku TEXT NOT NULL;
-- שגיאה בזמן ריצה: Cannot add a NOT NULL column with default value NULL

לעמודה קיימת אין שינוי במקום. המתכון המקובל הוא ריקוד הבנייה מחדש: יוצרים טבלה חדשה עם האילוץ הרצוי, מעתיקים את הנתונים, מוחקים את הישנה ומשנים את שם החדשה. נעסוק בזה בעמוד drop-and-alter-table; לעת עתה, רק דעו שהמגבלה אמיתית ותכננו את הסכמה שלכם בהתאם.

שילוב מציאותי

רוב טבלאות הייצור משתמשות בשני האילוצים יחד כדי לקודד "מה שהאפליקציה מצפה שיהיה נכון":

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer    TEXT    NOT NULL,
    total_cents INTEGER NOT NULL DEFAULT 0,
    currency    TEXT    NOT NULL DEFAULT 'USD',
    status      TEXT    NOT NULL DEFAULT 'pending',
    created_at  TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    notes       TEXT
);

INSERT INTO orders (customer) VALUES ('Ada');
INSERT INTO orders (customer, total_cents, currency) VALUES ('Boris', 4999, 'EUR');

SELECT id, customer, total_cents, currency, status, created_at FROM orders;

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

זה הערך של האילוצים האלה: הם הופכים הנחות לכללים שמסד הנתונים עצמו אוכף.

מלכודות נפוצות

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

• NULL מפורש מנטרל את DEFAULT. INSERT INTO t (col) VALUES (NULL) לא ישתמש בברירת המחדל. העמודה צריכה להיעדר מרשימת העמודות.
• ברירות מחדל שהן ביטויים צריכות סוגריים. DEFAULT CURRENT_TIMESTAMP עובד (זו אחת משלוש מילות המפתח המיוחדות). DEFAULT lower(hex(randomblob(8))) לא עובד: עטפו אותו: DEFAULT (lower(hex(randomblob(8)))).
• NOT NULL ומחרוזת ריקה הם דברים שונים. '' הוא ערך TEXT תקין ולא יפעיל את האילוץ. אם אתם רוצים לאסור גם מחרוזות ריקות, זו עבודה בשביל CHECK (בעמוד הבא).
• ADD COLUMN ... NOT NULL דורש DEFAULT שאינו NULL. בלי זה, SQLite מסרבת לשינוי.

הצעד הבא: אילוצי CHECK

NOT NULL ו-DEFAULT מכסים את "חייב להתקיים" ואת "למלא אם חסר". לשכבת האימות הבאה, "חייב להיות חיובי", "חייב להיות אחד מהערכים האלה", "תאריך הסיום חייב להיות אחרי תאריך ההתחלה", ל-SQLite יש אילוצי CHECK, שמאפשרים לכתוב ביטויים בוליאניים שרירותיים שכל שורה חייבת לקיים. זה העמוד הבא.