在 SQLite3 中,列的特殊屬性通常通過約束(Constraints)和數據類型修飾符來定義。這些屬性可以在創建表時指定,用于限制數據的完整性或定義特殊行為。以下是 SQLite3 支持的主要特殊屬性及其說明:
1. 主鍵約束(PRIMARY KEY)
- 功能:確保列中的值唯一且不為 NULL,通常用于標識表中的每一行。
- 示例:
CREATE TABLE users (id INTEGER PRIMARY KEY, -- 自增主鍵(見 AUTOINCREMENT)name TEXT NOT NULL );
- 特性:
- 若未顯式指定值,SQLite 會自動分配一個唯一的整數(ROWID)。
- 一個表只能有一個主鍵,但主鍵可以由多個列組成(復合主鍵)。
2. 自增屬性(AUTOINCREMENT)
- 功能:僅用于
INTEGER PRIMARY KEY
,確保值嚴格遞增且不會被重用(即使行被刪除)。 - 示例:
CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, -- 自增且不重用值message TEXT );
- 注意:
- 不使用
AUTOINCREMENT
時,SQLite 可能會重用已刪除的 ID 值(性能更佳)。 AUTOINCREMENT
會增加額外開銷,僅在需要嚴格遞增時使用。
- 不使用
3. 唯一約束(UNIQUE)
- 功能:確保列中的所有值都是唯一的,但允許有一個 NULL 值。
- 示例:
CREATE TABLE emails (user_id INTEGER PRIMARY KEY,email TEXT UNIQUE -- 每個郵箱必須唯一 );
4. 非空約束(NOT NULL)
- 功能:禁止列中插入 NULL 值。
- 示例:
CREATE TABLE products (id INTEGER PRIMARY KEY,name TEXT NOT NULL, -- 名稱不能為空price REAL );
5. 默認值(DEFAULT)
- 功能:當插入時未提供值,則使用預設的默認值。
- 示例:
CREATE TABLE tasks (id INTEGER PRIMARY KEY,status TEXT DEFAULT 'pending', -- 默認狀態為 "pending"created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 默認時間為當前時間 );
6. CHECK 約束
- 功能:自定義條件,確保插入或更新的值滿足特定表達式。
- 示例:
CREATE TABLE employees (id INTEGER PRIMARY KEY,salary REAL CHECK (salary > 0), -- 工資必須為正數age INTEGER CHECK (age >= 18) -- 年齡必須 >= 18 );
7. 外鍵約束(FOREIGN KEY)
- 功能:建立表間關聯,確保引用的值存在于關聯表中。
- 示例:
CREATE TABLE orders (order_id INTEGER PRIMARY KEY,user_id INTEGER,FOREIGN KEY (user_id) REFERENCES users(id) -- user_id 必須存在于 users 表的 id 列中 );
- 特性:
- 需要通過
PRAGMA foreign_keys = ON;
啟用外鍵約束。 - 可配合
ON DELETE
和ON UPDATE
子句定義級聯操作(如CASCADE
、SET NULL
)。
- 需要通過
8. 索引(INDEX)
- 功能:加速查詢,但不屬于嚴格的約束。
- 示例:
CREATE INDEX idx_emails ON emails(email); -- 為 email 列創建索引
9. 沖突處理
- 功能:當違反約束時的默認行為,可通過
ON CONFLICT
子句指定。 - 選項:
ROLLBACK
:回滾事務。ABORT
:中止當前操作(默認)。REPLACE
:替換已有記錄。IGNORE
:忽略沖突。
- 示例:
INSERT OR REPLACE INTO emails (user_id, email) VALUES (1, 'new@example.com');
10. 自動時間戳
- 功能:利用
DEFAULT CURRENT_TIMESTAMP
自動記錄時間。 - 示例:
CREATE TABLE sessions (id INTEGER PRIMARY KEY,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新時自動刷新 );
總結
屬性 | 作用 |
---|---|
PRIMARY KEY | 確保唯一性和非空性,通常作為行標識符。 |
AUTOINCREMENT | 強制整數主鍵嚴格遞增且不重用值。 |
UNIQUE | 確保列值唯一(允許一個 NULL)。 |
NOT NULL | 禁止插入 NULL 值。 |
DEFAULT value | 未提供值時使用默認值。 |
CHECK (expr) | 自定義約束條件。 |
FOREIGN KEY | 建立表間引用關系。 |
INDEX | 加速查詢(非約束)。 |
ON CONFLICT | 定義約束沖突時的處理策略。 |
使用建議
- 主鍵選擇:優先使用
INTEGER PRIMARY KEY
,性能最佳。僅在必要時添加AUTOINCREMENT
。 - 外鍵約束:啟用外鍵約束(
PRAGMA foreign_keys = ON
)以維護數據完整性。 - CHECK 約束:用于業務規則(如年齡范圍、金額限制),但避免復雜表達式以保持性能。