PostgreSQL约束条件设置

发布时间: 2025-04-08 09:43

↑

# PostgreSQL约束条件设置

本文将详细介绍PostgreSQL中的各种约束条件，包括它们的用途、设置方法和最佳实践，帮助你确保数据库中数据的完整性和一致性。

## 约束类型概述

PostgreSQL提供以下几种主要的约束类型：

1. NOT NULL约束
2. UNIQUE约束
3. PRIMARY KEY约束
4. FOREIGN KEY约束
5. CHECK约束
6. EXCLUSION约束

## NOT NULL约束

### 1. 基本用法

```sql
-- 创建表时设置NOT NULL
CREATE TABLE employees (
    employee_id serial PRIMARY KEY,
    first_name varchar(50) NOT NULL,
    last_name varchar(50) NOT NULL,
    email varchar(100) NOT NULL,
    hire_date date NOT NULL
);

-- 修改现有列添加NOT NULL
ALTER TABLE employees
ALTER COLUMN phone SET NOT NULL;

-- 删除NOT NULL约束
ALTER TABLE employees
ALTER COLUMN phone DROP NOT NULL;
```

### 2. 最佳实践

```sql
-- 设置默认值配合NOT NULL
CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    order_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status varchar(20) NOT NULL DEFAULT 'pending'
);
```

## UNIQUE约束

### 1. 单列唯一约束

```sql
-- 创建表时设置UNIQUE
CREATE TABLE users (
    user_id serial PRIMARY KEY,
    email varchar(100) UNIQUE,
    username varchar(50) UNIQUE
);

-- 添加UNIQUE约束
ALTER TABLE users
ADD CONSTRAINT users_phone_key UNIQUE (phone);
```

### 2. 多列唯一约束

```sql
-- 创建表时设置多列UNIQUE
CREATE TABLE employee_projects (
    employee_id integer,
    project_id integer,
    role varchar(50),
    UNIQUE (employee_id, project_id)
);

-- 添加多列UNIQUE约束
ALTER TABLE employee_projects
ADD CONSTRAINT unique_employee_project 
    UNIQUE (employee_id, project_id);
```

## PRIMARY KEY约束

### 1. 单列主键

```sql
-- 使用serial类型
CREATE TABLE products (
    product_id serial PRIMARY KEY,
    name varchar(100) NOT NULL
);

-- 显式指定主键
CREATE TABLE categories (
    category_id integer PRIMARY KEY,
    name varchar(50) NOT NULL
);
```

### 2. 复合主键

```sql
-- 创建表时设置复合主键
CREATE TABLE order_items (
    order_id integer,
    product_id integer,
    quantity integer NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- 添加复合主键
ALTER TABLE order_items
ADD PRIMARY KEY (order_id, product_id);
```

## FOREIGN KEY约束

### 1. 基本外键

```sql
-- 创建表时设置外键
CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    user_id integer REFERENCES users(user_id),
    order_date timestamp NOT NULL
);

-- 添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) 
    REFERENCES users(user_id);
```

### 2. 级联操作

```sql
-- 设置级联删除
CREATE TABLE comments (
    comment_id serial PRIMARY KEY,
    post_id integer 
        REFERENCES posts(post_id) 
        ON DELETE CASCADE,
    content text NOT NULL
);

-- 设置级联更新
CREATE TABLE order_details (
    detail_id serial PRIMARY KEY,
    order_id integer 
        REFERENCES orders(order_id) 
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);
```

## CHECK约束

### 1. 简单检查

```sql
-- 基本CHECK约束
CREATE TABLE products (
    product_id serial PRIMARY KEY,
    name varchar(100) NOT NULL,
    price decimal(10,2) CHECK (price > 0),
    stock integer CHECK (stock >= 0)
);

-- 添加CHECK约束
ALTER TABLE products
ADD CONSTRAINT check_price 
    CHECK (price <= 1000000);
```

### 2. 复杂检查

```sql
-- 多条件CHECK约束
CREATE TABLE employees (
    employee_id serial PRIMARY KEY,
    first_name varchar(50) NOT NULL,
    last_name varchar(50) NOT NULL,
    salary decimal(10,2),
    hire_date date,
    CONSTRAINT valid_salary 
        CHECK (salary > 0 AND salary < 1000000),
    CONSTRAINT valid_dates 
        CHECK (hire_date <= CURRENT_DATE)
);

-- 使用函数的CHECK约束
CREATE TABLE users (
    user_id serial PRIMARY KEY,
    email varchar(100),
    CONSTRAINT valid_email 
        CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);
```

## EXCLUSION约束

### 1. 基本用法

```sql
-- 创建btree_gist扩展
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 使用EXCLUDE约束
CREATE TABLE meetings (
    meeting_id serial PRIMARY KEY,
    room_id integer,
    start_time timestamp,
    end_time timestamp,
    EXCLUDE USING gist (
        room_id WITH =,
        tsrange(start_time, end_time) WITH &&
    )
);
```

## 约束管理

### 1. 查看约束

```sql
-- 查看表的所有约束
SELECT 
    conname as constraint_name,
    contype as constraint_type,
    pg_get_constraintdef(oid) as definition
FROM pg_constraint
WHERE conrelid = 'table_name'::regclass;
```

### 2. 删除约束

```sql
-- 删除命名约束
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

-- 删除主键
ALTER TABLE table_name
DROP CONSTRAINT table_name_pkey;
```

## 最佳实践

### 1. 命名约定

```sql
-- 使用描述性约束名称
CREATE TABLE orders (
    order_id serial,
    user_id integer,
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_users 
        FOREIGN KEY (user_id) 
        REFERENCES users(user_id),
    CONSTRAINT check_order_amount 
        CHECK (amount > 0)
);
```

### 2. 性能考虑

```sql
-- 创建部分唯一索引而不是约束
CREATE UNIQUE INDEX idx_active_users_email 
ON users(email) WHERE status = 'active';

-- 使用适当的约束类型
CREATE TABLE audit_logs (
    log_id serial,
    created_at timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id)
    -- 避免不必要的约束
);
```

### 3. 数据迁移

```sql
-- 添加约束前验证数据
BEGIN;
    -- 检查现有数据
    SELECT count(*) 
    FROM products 
    WHERE price <= 0;
    
    -- 如果没有违规数据，添加约束
    ALTER TABLE products
    ADD CONSTRAINT positive_price CHECK (price > 0);
COMMIT;
```

## 总结

合理使用约束可以：

1. 确保数据完整性
2. 防止无效数据输入
3. 维护数据一致性
4. 提供业务规则实现

在设计数据库时，应该：

1. 仔细规划约束策略
2. 使用合适的约束类型
3. 考虑性能影响
4. 遵循命名约定
5. 定期维护和检查约束

通过正确使用约束，可以构建更可靠、更健壮的数据库应用。在实际应用中，要根据具体需求选择合适的约束类型，并注意约束对性能的影响。

元素码农