SQLite DROP TABLE ו-ALTER TABLE: הסבר על שינויי סכמה

סכמות משתנות. SQLite מאפשרת לשנות אותן, ברוב המקרים.

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

הקאץ': ALTER TABLE של SQLite מוגבלת הרבה יותר מאשר ב-Postgres או ב-MySQL. לדעת מה היא יכולה ומה לא, ואת דפוס הבנייה מחדש לדברים שהיא לא יכולה, זה רוב המיומנות כאן.

DROP TABLE מסירה טבלה וכל מה שמחובר אליה

DROP TABLE מוחקת את הטבלה, את השורות שלה, את האינדקסים שלה וכל trigger שהוגדר עליה. אין ביטול:

CREATE TABLE scratch (
    id INTEGER PRIMARY KEY,
    note TEXT
);

INSERT INTO scratch (note) VALUES ('temporary'), ('also temporary');

DROP TABLE scratch;

SELECT name FROM sqlite_master WHERE type = 'table';

הטבלה נעלמה. שאילתה עליה עכשיו תזרוק no such table: scratch.

אם אתם לא בטוחים אם הטבלה קיימת, מצב נפוץ בסקריפטים של הקמה, השתמשו ב-IF EXISTS כדי שהפקודה לא תעשה כלום בשקט כשהיא חסרה:

CREATE TABLE scratch (id INTEGER PRIMARY KEY);

DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS scratch;
SELECT 'both drops succeeded' AS result;

בלי IF EXISTS, המחיקה השנייה הייתה נכשלת בשגיאה. איתו, שתיהן רצות בלי בעיה.

מפתחות זרים יכולים לחסום DROP

אם אכיפת מפתחות זרים פועלת (PRAGMA foreign_keys = ON;) וטבלה אחרת מפנה לזו שאתם מוחקים, המחיקה נכשלת:

sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE users;
Runtime error: FOREIGN KEY constraint failed

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

ALTER TABLE: ארבעת הדברים שהיא יכולה לעשות

ALTER TABLE של SQLite תומכת בדיוק בארבע פעולות:

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

-- 1. שינוי שם הטבלה
ALTER TABLE posts RENAME TO articles;

-- 2. שינוי שם של עמודה
ALTER TABLE articles RENAME COLUMN body TO content;

-- 3. הוספת עמודה
ALTER TABLE articles ADD COLUMN published_at TEXT;

-- 4. מחיקת עמודה (SQLite 3.35+)
ALTER TABLE articles DROP COLUMN published_at;

SELECT sql FROM sqlite_master WHERE name = 'articles';

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

ADD COLUMN עם ערך ברירת מחדל

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

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

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

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM users;

שתי השורות הקיימות מקבלות 'active'. ערך ברירת המחדל חייב להיות קבוע: SQLite לא תאפשר להשתמש ב-CURRENT_TIMESTAMP או בכל ביטוי לא קבוע אחר כברירת מחדל ב-ADD COLUMN, כי היא צריכה ערך שאפשר להחיל על כל שורה קיימת בלי לחשב אותו לכל שורה.

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

מה ALTER TABLE לא יכולה לעשות

דברים שעובדים ב-Postgres או ב-MySQL אבל לא ב-SQLite:

• לשנות טיפוס של עמודה (ALTER COLUMN ... TYPE ...).
• לשנות את ערך ברירת המחדל של עמודה במקום.
• להוסיף או להסיר NOT NULL, CHECK, UNIQUE או PRIMARY KEY בעמודה קיימת.
• להוסיף מפתח זר לעמודה קיימת.
• לשנות את סדר העמודות.

ניסיון לעשות אחד מאלה נותן שגיאת תחביר. ל-SQLite אין בכלל פסוקית ALTER COLUMN. התשובה הרשמית זהה לכולם: לבנות את הטבלה מחדש.

דפוס הבנייה מחדש

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

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

INSERT INTO users (email, age) VALUES ('ada@example.com', '36'), ('boris@example.com', '41');

BEGIN;

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER CHECK (age > 0)
);

INSERT INTO users_new (id, email, age)
SELECT id, email, CAST(age AS INTEGER) FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

עכשיו users.age הוא מספר שלם עם אילוץ check, ו-email הוא NOT NULL. הנתונים הגיעו יחד איתם.

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

• כבו מפתחות זרים לכל משך הפעולה. אם טבלאות אחרות מפנות לשלכם, הריצו PRAGMA foreign_keys = OFF; לפני הטרנזקציה ו-PRAGMA foreign_keys = ON; אחריה. אחרת ה-DROP TABLE ייכשל. אי אפשר לשנות את ה-pragma בתוך טרנזקציה, אז הגדירו אותו מחוץ לה.
• צרו מחדש אינדקסים ו-triggers. מחיקת הטבלה הישנה מוחקת גם את האינדקסים וה-triggers שלה. הוסיפו אותם שוב לטבלה החדשה אחרי שינוי השם.
• בדקו views. views שמפנים לטבלה עדיין מצביעים על השם הישן ב-SQL השמור שלהם. בנו מחדש כל view שתלוי בעמודות שהשתנו.

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

מחיקת כמה טבלאות

אין פקודה יחידה למחיקת כמה טבלאות: מריצים DROP TABLE לכל אחת. בתוך טרנזקציה, אם רוצים שהן יקובצו יחד:

CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
CREATE TABLE c (id INTEGER);

BEGIN;
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
COMMIT;

SELECT name FROM sqlite_master WHERE type = 'table';

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

מה לקחת מכאן

• DROP TABLE מסירה טבלה ואת האינדקסים וה-triggers שלה. השתמשו ב-IF EXISTS לסקריפטים אידמפוטנטיים.
• ALTER TABLE עושה רק ארבעה דברים: שינוי שם טבלה, שינוי שם עמודה, הוספת עמודה, מחיקת עמודה.
• לכל השאר, שינויי טיפוס, אילוצים חדשים, מפתחות זרים על עמודות קיימות, בנו את הטבלה מחדש בתוך טרנזקציה.
• שימו לב למפתחות זרים, לאינדקסים, ל-triggers ול-views כשבונים מחדש. הם לא עוברים עם הנתונים אוטומטית.

הבא בתור: הכנסת נתונים

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