约束

学习MySQL数据完整性约束

全站首页 频道首页

约束概述

约束是用于限制表中数据的规则，确保数据的准确性和可靠性。MySQL支持多种约束类型，用于维护数据的完整性。

约束类型

约束类型 描述 作用
NOT NULL 非空约束 确保列不能包含NULL值
UNIQUE 唯一约束 确保列中的所有值都是唯一的
PRIMARY KEY 主键约束 唯一标识表中的每条记录
FOREIGN KEY 外键约束 确保表之间的引用完整性
CHECK 检查约束 确保列中的值满足特定条件
DEFAULT 默认约束 为列设置默认值

NOT NULL约束

NOT NULL约束强制列不接受NULL值。

创建NOT NULL约束

SQL示例 
-- 创建表时定义NOT NULL约束
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    phone_number VARCHAR(20)  -- 允许NULL
);

修改NOT NULL约束

SQL示例 
-- 添加NOT NULL约束
ALTER TABLE users
MODIFY COLUMN phone_number VARCHAR(20) NOT NULL;

-- 移除NOT NULL约束
ALTER TABLE users
MODIFY COLUMN phone_number VARCHAR(20) NULL;

UNIQUE约束

UNIQUE约束确保列中的所有值都是不同的。

创建UNIQUE约束

SQL示例 
-- 创建表时定义UNIQUE约束
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    phone_number VARCHAR(20) UNIQUE
);

表级UNIQUE约束

SQL示例 
-- 创建复合UNIQUE约束
CREATE TABLE user_addresses (
    address_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    address_type VARCHAR(20) NOT NULL,
    address VARCHAR(255) NOT NULL,
    UNIQUE (user_id, address_type)  -- 同一用户不能有相同类型的多个地址
);

添加UNIQUE约束

SQL示例 
-- 为现有列添加UNIQUE约束
ALTER TABLE users
ADD UNIQUE (username);

-- 为现有列添加命名UNIQUE约束
ALTER TABLE users
ADD CONSTRAINT uk_users_email UNIQUE (email);

-- 添加复合UNIQUE约束
ALTER TABLE user_addresses
ADD CONSTRAINT uk_user_address_type UNIQUE (user_id, address_type);

删除UNIQUE约束

SQL示例 
-- 删除UNIQUE约束
ALTER TABLE users
DROP INDEX username;

-- 删除命名UNIQUE约束
ALTER TABLE users
DROP INDEX uk_users_email;

PRIMARY KEY约束

PRIMARY KEY约束唯一标识表中的每条记录。主键必须包含唯一值，且不能包含NULL值。

创建PRIMARY KEY约束

SQL示例 
-- 创建表时定义单列主键
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 使用AUTO_INCREMENT
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

复合主键

SQL示例 
-- 创建复合主键
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)  -- 复合主键
);

添加PRIMARY KEY约束

SQL示例 
-- 为现有表添加主键
ALTER TABLE customers
ADD PRIMARY KEY (customer_id);

-- 为现有表添加复合主键
ALTER TABLE order_items
ADD PRIMARY KEY (order_id, product_id);

删除PRIMARY KEY约束

SQL示例 
-- 删除主键约束
ALTER TABLE users
DROP PRIMARY KEY;
注意：删除主键约束时，如果主键列有AUTO_INCREMENT属性，需要先移除该属性。

FOREIGN KEY约束

FOREIGN KEY约束用于防止破坏表之间关系的操作，保持引用完整性。

创建FOREIGN KEY约束

SQL示例 
-- 创建表时定义外键
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

外键操作选项

外键可以定义在更新或删除父表记录时的操作：

SQL示例 
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE    -- 删除订单时自动删除订单项
        ON UPDATE CASCADE,   -- 更新订单ID时自动更新订单项
    FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE SET NULL   -- 删除产品时将product_id设为NULL
        ON UPDATE RESTRICT   -- 阻止更新产品ID
);

外键操作类型

操作 描述
RESTRICT 拒绝删除或更新父表记录（默认）
CASCADE 级联删除或更新子表记录
SET NULL 将子表的外键列设置为NULL
NO ACTION 与RESTRICT相同
SET DEFAULT 将子表的外键列设置为默认值

添加FOREIGN KEY约束

SQL示例 
-- 为现有表添加外键
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

删除FOREIGN KEY约束

SQL示例 
-- 删除外键约束
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customers;

查看外键信息

SQL示例 
-- 查看表的外键
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'your_database'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

CHECK约束

CHECK约束用于限制列中的值的范围。

SQL示例 
-- 创建表时定义CHECK约束
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),  -- 价格必须大于0
    stock_quantity INT CHECK (stock_quantity >= 0)  -- 库存不能为负数
);

复杂CHECK约束

SQL示例 
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2),
    department VARCHAR(50),
    CONSTRAINT chk_salary_range CHECK (salary >= 0 AND salary <= 1000000),
    CONSTRAINT chk_department CHECK (department IN ('IT', 'HR', 'Finance', 'Marketing'))
);

添加CHECK约束

SQL示例 
-- 为现有表添加CHECK约束
ALTER TABLE products
ADD CONSTRAINT chk_price_positive
CHECK (price > 0);

DEFAULT约束

DEFAULT约束用于为列设置默认值。

创建DEFAULT约束

SQL示例 
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    login_count INT DEFAULT 0
);

修改DEFAULT约束

SQL示例 
-- 添加DEFAULT约束
ALTER TABLE users
ALTER COLUMN is_active SET DEFAULT 1;

-- 删除DEFAULT约束
ALTER TABLE users
ALTER COLUMN is_active DROP DEFAULT;

约束管理

查看约束

SQL示例 
-- 查看表的约束信息
SELECT 
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    TABLE_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_database'
    AND TABLE_NAME = 'your_table';

禁用和启用约束

在某些情况下，可能需要临时禁用约束以提高性能或进行数据迁移。

SQL示例 
-- 禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;

-- 执行需要禁用外键的操作
-- ...

-- 启用外键检查
SET FOREIGN_KEY_CHECKS = 1;

约束最佳实践

设计原则

  • 选择合适的主键 - 使用有意义的业务键或代理键
  • 适当使用外键 - 维护引用完整性，但注意性能影响
  • 合理使用NOT NULL - 对必填字段使用NOT NULL约束
  • 使用唯一约束 - 确保业务数据的唯一性

性能考虑

  • 外键约束会影响插入、更新和删除操作的性能
  • 在大量数据导入时，可以临时禁用约束
  • 为外键列创建索引以提高查询性能
  • 定期检查约束的有效性

命名规范

为约束使用有意义的名称，便于管理和维护：

约束类型 命名模式 示例
主键 pk_table_column pk_users_id
外键 fk_child_parent fk_orders_customers
唯一约束 uk_table_column uk_users_email
检查约束 chk_table_condition chk_products_price
约束使用建议：
  • 在数据库设计阶段就定义好约束
  • 使用约束确保数据质量，而不是依赖应用程序
  • 定期验证约束的有效性
  • 在文档中记录约束的设计决策

约束与性能优化

约束虽然能保证数据完整性，但也会对性能产生一定影响：

  • 主键约束 - 自动创建唯一索引，提高查询性能
  • 外键约束 - 需要检查引用完整性，可能降低写入性能
  • 唯一约束 - 自动创建唯一索引，提高查询性能但可能降低写入性能
  • 检查约束 - 需要验证条件，可能降低写入性能

约束与事务

约束在事务中的行为：

  • 约束检查在事务提交时进行
  • 可以使用SET CONSTRAINTS语句延迟约束检查
  • 违反约束会导致事务回滚

约束在不同存储引擎中的支持

不同存储引擎对约束的支持程度不同：

约束类型 InnoDB MyISAM MEMORY
主键约束 完全支持 完全支持 完全支持
外键约束 完全支持 不支持 不支持
唯一约束 完全支持 完全支持 完全支持
检查约束 MySQL 8.0+支持 部分支持 部分支持

下一步学习

掌握了约束后，您可以继续学习：

  • 索引 - 学习如何提高查询性能
  • 事务 - 学习如何保证数据一致性
  • 性能优化 - 学习数据库性能优化技巧