מפתחות ראשיים ב-SQLite: INTEGER, מפתח מורכב ו-AUTOINCREMENT

מה מפתח ראשי עושה בפועל

מפתח ראשי הוא העמודה (או צירוף העמודות) שמזהה באופן ייחודי כל שורה בטבלה. לשתי שורות אסור שיהיה אותו ערך מפתח ראשי. SQLite אוכפת את זה בשבילכם, ומשתמשת במפתח כדי למצוא שורות מהר.

הצורה הפשוטה ביותר נכתבת ישירות על עמודה:

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

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

SELECT * FROM users;

לא סיפקתם id, ו-SQLite מילאה אחד. זה לא קסם: זה מקרה מיוחד של INTEGER PRIMARY KEY שכדאי להבין לפני שכותבים משהו אחר.

INTEGER PRIMARY KEY הוא מיוחד

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

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

INSERT INTO posts (title) VALUES ('First'), ('Second'), ('Third');

SELECT id, rowid, title FROM posts;

id ו-rowid הם אותה עמודה בשני שמות. חיפושים לפי id הולכים ישר לשורה, אין עץ שני לעבור בו. זו הסיבה שהעצה הסטנדרטית ל-SQLite היא: אם אתם רוצים מפתח ראשי מספרי, כתבו בדיוק INTEGER PRIMARY KEY. לא INT, לא BIGINT, לא INTEGER NOT NULL PRIMARY KEY (טוב, זה עובד, אבל הטיפוס חייב להיות INTEGER).

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

בדרך כלל אין צורך ב-AUTOINCREMENT

רפלקס נפוץ ממסדי נתונים אחרים הוא לכתוב id INTEGER PRIMARY KEY AUTOINCREMENT. ב-SQLite, מילת המפתח AUTOINCREMENT עושה משהו צר יותר ממה שהשם מרמז, וברוב המקרים לא צריך אותה.

בלי AUTOINCREMENT, עמודת INTEGER PRIMARY KEY מתמלאת אוטומטית במספר שגדול באחד מה-rowid הגדול ביותר הקיים. אם מוחקים את השורה האחרונה, ההכנסה הבאה עשויה להשתמש שוב באותו מזהה.

עם AUTOINCREMENT, SQLite עוקבת אחרי המזהה הגבוה ביותר שאי פעם היה בשימוש בטבלה צדדית בשם sqlite_sequence ולעולם לא משתמשת שוב בערכים, גם אחרי מחיקה.

CREATE TABLE plain (id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE strict_id (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT);

INSERT INTO plain (label) VALUES ('a'), ('b'), ('c');
INSERT INTO strict_id (label) VALUES ('a'), ('b'), ('c');

DELETE FROM plain WHERE id = 3;
DELETE FROM strict_id WHERE id = 3;

INSERT INTO plain (label) VALUES ('new');
INSERT INTO strict_id (label) VALUES ('new');

SELECT 'plain' AS table_name, id, label FROM plain
UNION ALL
SELECT 'strict_id', id, label FROM strict_id;

הטבלה plain השתמשה שוב במזהה 3. הטבלה עם AUTOINCREMENT קפצה ל-4. אלא אם יש לכם סיבה אמיתית לאסור שימוש חוזר במזהים, כמו ביקורת או הפניות חיצוניות שנשארות אחרי מחיקה, ותרו על AUTOINCREMENT. הוא עולה בכתיבה נוספת בכל הכנסה ובטבלת רישום נפרדת.

מפתחות ראשיים מורכבים

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

CREATE TABLE user_roles (
    user_id  INTEGER NOT NULL,
    role_id  INTEGER NOT NULL,
    granted_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

INSERT INTO user_roles (user_id, role_id) VALUES (1, 10), (1, 20), (2, 10);
-- זו מפרה את המפתח המורכב:
INSERT INTO user_roles (user_id, role_id) VALUES (1, 10);

הזוג חייב להיות ייחודי בכל הטבלה: (1, 10) יכול להופיע רק פעם אחת. כל עמודה לבדה יכולה לחזור בחופשיות. זו בדיוק הנקודה: לכל משתמש יכולים להיות תפקידים רבים, לכל תפקיד יכולים להיות משתמשים רבים, אבל צירוף מסוים של משתמש ותפקיד קיים לכל היותר פעם אחת.

מפתח ראשי מורכב יוצר אינדקס נפרד שמכסה את העמודות הרשומות. הוא לא הופך ל-rowid: רק INTEGER PRIMARY KEY בודד מקבל את היחס הזה.

המלכודת של NULL במפתח ראשי

הנה מוזרות שמפתיעה אנשים שמגיעים מ-PostgreSQL או מ-MySQL: בטבלת SQLite רגילה, עמודת מפתח ראשי שאינה INTEGER PRIMARY KEY יכולה להכיל NULL. זה באג ותיק שמחברי SQLite השאירו לטובת תאימות לאחור.

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Apple');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystery 1');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystery 2');

SELECT * FROM products;

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

CREATE TABLE products (
    sku   TEXT NOT NULL PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Apple');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystery');  -- עכשיו נדחה

או להשתמש בטבלת STRICT, שבה הבאג של NULL במפתח ראשי מתוקן. ההרגל לכתוב NOT NULL על כל עמודת מפתח ראשי הוא ביטוח זול.

מפתח ראשי מול UNIQUE

שניהם מונעים כפילויות. ההבדלים:

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

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

INSERT INTO accounts (email, username) VALUES ('a@x.com', 'ada');
INSERT INTO accounts (email, username) VALUES ('b@x.com', 'boris');
SELECT * FROM accounts;

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

הוספת מפתח ראשי מאוחר יותר (בעיקר: אל תעשו את זה)

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

CREATE TABLE events (
    name TEXT,
    occurred_at TEXT
);
INSERT INTO events VALUES ('login', '2026-01-01'), ('logout', '2026-01-02');

-- בנייה מחדש עם מפתח ראשי:
CREATE TABLE events_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);
INSERT INTO events_new (name, occurred_at) SELECT name, occurred_at FROM events;
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;

SELECT * FROM events;

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

רשימת בדיקה מהירה

כשאתם כותבים טבלה חדשה, שאלו:

• האם לשורה יש מזהה ייחודי טבעי? אם זה מספר שלם יחיד, השתמשו ב-INTEGER PRIMARY KEY.
• האם הזהות היא בעצם צירוף של עמודות (טבלת קישור)? השתמשו ב-PRIMARY KEY (col_a, col_b) ברמת הטבלה.
• האם המפתח הוא טקסט או טיפוס אחר שאינו מספר שלם? הוסיפו NOT NULL במפורש.
• האם אתם באמת צריכים AUTOINCREMENT? כנראה שלא.
• האם הטבלה קטנה, בעיקר לקריאה, ועם מפתח ראשי שאינו מספר שלם? שקלו WITHOUT ROWID (מוסבר בעמוד על rowid).

הבא: rowid

INTEGER PRIMARY KEY הופיע לרגע בתור "כינוי ל-rowid", אבל rowid הוא הבסיס שמתחת לכל טבלת SQLite רגילה, וכדאי להבין אותו ישירות. זה העמוד הבא.