python：唯一值 email TEXT UNIQUE

在 SQLite 中，email TEXT UNIQUE 是用于定义表结构时的约束条件，它确保 email 列中的所有值都是唯一的，防止重复的电子邮件地址插入表中。以下是关于这个约束的详细解释和示例：

1. 约束作用

• 唯一性：UNIQUE 约束确保表中任意两行的 email 列值都不相同。
• 空值处理：UNIQUE 允许 NULL 值存在（多个 NULL 被视为不同值）。若要禁止空值，需同时使用 NOT NULL。

2. 创建带唯一约束的表

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT UNIQUE  -- 确保 email 列不重复
);

3. 插入数据时的唯一性检查

当插入重复的 email 时，会抛出 IntegrityError。

示例代码：

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# 创建表
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT UNIQUE
)
''')

# 插入第一条数据
try:
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", 
                  ("Alice", "alice@example.com"))
    conn.commit()
    print("第一条数据插入成功")
except sqlite3.IntegrityError:
    print("错误：email 已存在")

# 插入重复 email
try:
    cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", 
                  ("Bob", "alice@example.com"))  # 重复 email
    conn.commit()
except sqlite3.IntegrityError as e:
    print(f"错误：{e}")  # 输出：UNIQUE constraint failed: users.email

conn.close()

4. 与 PRIMARY KEY 的区别

特性	PRIMARY KEY	UNIQUE 约束
唯一性	必须唯一	必须唯一
NULL 值	不允许 NULL	允许 NULL（多个 NULL 被视为不同值）
自动索引	自动创建主键索引	自动创建唯一索引
表中数量限制	每个表只能有一个 PRIMARY KEY	可以有多个 UNIQUE 约束

5. 修改现有表添加 UNIQUE 约束

如果表已存在，可以通过以下方式添加约束：

方法一：创建新表并迁移数据

-- 创建新表
CREATE TABLE new_users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT UNIQUE
);

-- 复制数据（过滤重复项）
INSERT INTO new_users (id, name, email)
SELECT id, name, email FROM users
GROUP BY email;  -- 仅保留每个 email 的第一条记录

-- 删除旧表并重命名新表
DROP TABLE users;
ALTER TABLE new_users RENAME TO users;

方法二：添加唯一索引

CREATE UNIQUE INDEX idx_email ON users (email);

6. 处理唯一约束冲突

插入数据时，可以使用 ON CONFLICT 子句处理冲突：

-- 替换已存在的记录（删除旧记录并插入新记录）
INSERT OR REPLACE INTO users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');

-- 忽略冲突（不插入新记录）
INSERT OR IGNORE INTO users (name, email) VALUES ('Bob', 'alice@example.com');

Python 示例：

try:
    # 忽略冲突
    cursor.execute("INSERT OR IGNORE INTO users (name, email) VALUES (?, ?)", 
                  ("Bob", "alice@example.com"))
except sqlite3.IntegrityError:
    pass  # 不会触发异常，冲突时静默失败

7. 查询重复数据

若需要查找表中重复的 email，可以使用分组查询：

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

总结

• 适用场景：email TEXT UNIQUE 适用于需要确保邮箱地址不重复的场景（如用户表）。
• 错误处理：插入重复值时会触发 sqlite3.IntegrityError，需通过 try-except 捕获或使用 ON CONFLICT 子句处理。
• 性能影响：唯一约束会自动创建索引，提高查询速度，但可能略微降低写入性能。