约束与索引 - 元素码农

发布时间: 2025-04-02 22:51

↑

# 约束与索引

本文将详细介绍SQLite中的约束和索引，包括各种类型的约束、索引的创建和使用，以及它们在数据完整性和查询性能优化中的应用。

## 约束类型

### 主键约束（PRIMARY KEY）

1. **单字段主键**
   ```sql
   CREATE TABLE users (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       username TEXT NOT NULL
   );
   ```

2. **复合主键**
   ```sql
   CREATE TABLE order_items (
       order_id INTEGER,
       item_id INTEGER,
       quantity INTEGER NOT NULL,
       PRIMARY KEY (order_id, item_id)
   );
   ```

### 唯一约束（UNIQUE）

1. **单字段唯一**
   ```sql
   CREATE TABLE users (
       id INTEGER PRIMARY KEY,
       email TEXT UNIQUE,
       username TEXT NOT NULL
   );
   ```

2. **复合唯一**
   ```sql
   CREATE TABLE employee_projects (
       employee_id INTEGER,
       project_id INTEGER,
       role TEXT,
       UNIQUE (employee_id, project_id)
   );
   ```

### 非空约束（NOT NULL）

```sql
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    description TEXT
);
```

### 默认值约束（DEFAULT）

```sql
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    order_date TEXT DEFAULT CURRENT_TIMESTAMP,
    status TEXT DEFAULT 'pending'
);
```

### 检查约束（CHECK）

```sql
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price > 0),
    stock INTEGER CHECK (stock >= 0)
);
```

### 外键约束（FOREIGN KEY）

```sql
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    total_amount REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);
```

## 索引操作

### 创建索引

1. **单字段索引**
   ```sql
   -- 普通索引
   CREATE INDEX idx_users_email ON users(email);
   
   -- 唯一索引
   CREATE UNIQUE INDEX idx_users_username ON users(username);
   ```

2. **复合索引**
   ```sql
   CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
   ```

3. **部分索引**
   ```sql
   -- 只为活跃用户创建索引
   CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
   ```

### 删除索引

```sql
DROP INDEX IF EXISTS idx_users_email;
```

### 重建索引

```sql
REINDEX table_name;
-- 或者
REINDEX index_name;
```

## 约束管理

### 添加约束

1. **添加非空约束**
   ```sql
   -- 先确保没有NULL值
   UPDATE table_name SET column_name = default_value WHERE column_name IS NULL;
   
   -- 添加非空约束
   ALTER TABLE table_name MODIFY column_name data_type NOT NULL;
   ```

2. **添加外键约束**
   ```sql
   -- 启用外键支持
   PRAGMA foreign_keys = ON;
   
   -- 添加外键约束
   ALTER TABLE orders ADD CONSTRAINT fk_user
   FOREIGN KEY (user_id) REFERENCES users(id);
   ```

### 删除约束

```sql
-- 删除主键约束
ALTER TABLE table_name DROP PRIMARY KEY;

-- 删除唯一约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
```

## 性能优化

### 索引优化

1. **选择合适的索引列**
   - 高选择性的列
   - 经常用于查询条件的列
   - 经常用于排序或分组的列

2. **避免过度索引**
   - 不要索引低选择性的列
   - 考虑维护成本
   - 权衡存储空间

### 约束优化

1. **合理使用外键**
   - 确保引用完整性
   - 考虑级联操作的影响
   - 适当时使用延迟约束

2. **检查约束使用**
   - 保证数据有效性
   - 避免过于复杂的检查条件
   - 考虑性能影响

## 最佳实践

### 约束命名

```sql
-- 主键约束
CONSTRAINT pk_table_name PRIMARY KEY (column_name)

-- 外键约束
CONSTRAINT fk_table_reference FOREIGN KEY (column_name)
    REFERENCES other_table(column_name)

-- 唯一约束
CONSTRAINT uq_table_column UNIQUE (column_name)
```

### 索引命名

```sql
-- 单字段索引
CREATE INDEX idx_table_column ON table_name(column_name);

-- 复合索引
CREATE INDEX idx_table_col1_col2 ON table_name(column1, column2);
```

### 维护建议

1. **定期检查**
   - 监控索引使用情况
   - 分析查询性能
   - 评估约束有效性

2. **优化策略**
   - 删除未使用的索引
   - 合并重复的约束
   - 优化约束条件

## 总结

合理使用约束和索引可以：

- 保证数据完整性
- 提高查询性能
- 优化存储结构
- 简化应用逻辑

建议：

- 根据实际需求选择约束
- 合理设计索引策略
- 定期维护和优化
- 注意性能平衡