当前位置：首页 > news >正文

SQLite3 中列(变量)的特殊属性

news 2025/7/14 12:30:10

在 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`	定义约束冲突时的处理策略。