【MySQL】数据表的约束-优快云博客

本文链接：https://blog.youkuaiyun.com/Go_ahead1025/article/details/147839496

在这里插入图片描述

个人主页：Guiat
归属专栏：MySQL

在这里插入图片描述

文章目录

1. 约束概述
- 1.1 约束的作用
- 1.2 MySQL中的约束类型
2. 主键约束 (PRIMARY KEY)
- 2.1 主键的特点
- 2.2 创建主键约束
- 2.3 为现有表添加主键
- 2.4 删除主键
- 2.5 自动递增主键
3. 外键约束 (FOREIGN KEY)
- 3.1 外键的作用
- 3.2 创建外键约束
- 3.3 为现有表添加外键
- 3.4 级联操作
- 3.5 删除外键约束
4. 唯一约束 (UNIQUE)
- 4.1 唯一约束的特点
- 4.2 创建唯一约束
- 4.3 为现有表添加唯一约束
- 4.4 删除唯一约束
5. 非空约束 (NOT NULL)
- 5.1 非空约束的特点
- 5.2 创建非空约束
- 5.3 为现有列添加非空约束
- 5.4 删除非空约束
6. 检查约束 (CHECK)
- 6.1 检查约束的特点
- 6.2 创建检查约束
- 6.3 为现有表添加检查约束
- 6.4 删除检查约束
7. 默认约束 (DEFAULT)
- 7.1 默认约束的特点
- 7.2 创建默认约束
- 7.3 为现有列添加默认约束
- 7.4 删除默认约束
8. 约束组合使用
- 8.1 综合运用约束
- 8.2 约束的优先级和执行顺序
9. 实际应用案例
- 9.1 用户和权限管理系统
- 9.2 电子商务产品目录
- 9.3 医疗预约系统
10. 约束最佳实践
- 10.1 命名约定
- 10.2 性能考量
- 10.3 可维护性建议
- 10.4 约束与数据迁移
- 10.5 常见问题及解决方案

正文

1. 约束概述

约束是数据库中用于维护数据完整性的规则，它们限制了可以存入表中的数据类型。MySQL约束确保数据的准确性和可靠性。

1.1 约束的作用

强制执行数据完整性
防止无效数据进入数据库
确保数据库状态的一致性
实现业务规则和逻辑关系

1.2 MySQL中的约束类型

MySQL支持以下几种主要的约束类型：

2. 主键约束 (PRIMARY KEY)

2.1 主键的特点

唯一标识表中的每一行记录
不允许NULL值
每个表只能有一个主键
通常使用索引来提高查询性能

2.2 创建主键约束

-- 在创建表时定义单列主键
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

-- 在创建表时定义复合主键
CREATE TABLE course_enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)
);

-- 使用表级约束语法
CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    PRIMARY KEY (product_id)
);

2.3 为现有表添加主键

-- 为现有表添加主键
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

-- 添加复合主键
ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2);

2.4 删除主键

-- 删除表的主键
ALTER TABLE table_name
DROP PRIMARY KEY;

2.5 自动递增主键

自动递增是主键的常用特性，可以自动生成唯一的ID值：

-- 创建带自动递增的主键
CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

-- 为现有表添加自动递增属性
ALTER TABLE table_name
MODIFY column_name INT AUTO_INCREMENT;

-- 设置AUTO_INCREMENT的起始值
ALTER TABLE table_name
AUTO_INCREMENT = 1000;

3. 外键约束 (FOREIGN KEY)

3.1 外键的作用

建立和加强表之间的关联
确保引用完整性
防止删除或修改被其他表引用的数据
帮助维护数据库的一致性

3.2 创建外键约束

-- 创建父表
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

-- 创建带外键的子表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 创建带命名外键约束的表
CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    department_id INT,
    CONSTRAINT fk_department
    FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

3.3 为现有表添加外键

-- 为现有表添加外键
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(department_id);

3.4 级联操作

外键可以配置在参考行被更新或删除时采取的操作：

-- 带CASCADE操作的外键
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) 
    REFERENCES customers(customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- 为现有表添加带级联操作的外键
ALTER TABLE order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id)
REFERENCES orders(order_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

外键级联操作的选项:

操作	描述
CASCADE	自动删除或更新子表中匹配的行
SET NULL	将子表中的外键列设置为NULL
SET DEFAULT	将子表中的外键列设置为默认值
RESTRICT	阻止删除或更新父表（默认行为）
NO ACTION	类似RESTRICT，不同之处在于检查时机

3.5 删除外键约束

-- 删除外键约束
ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;

4. 唯一约束 (UNIQUE)

4.1 唯一约束的特点

确保列中的所有值都是唯一的
允许NULL值（区别于主键）
可以应用于多个列
一个表可以有多个唯一约束

4.2 创建唯一约束

-- 在创建表时定义唯一约束
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20)
);

-- 使用表级约束语法
CREATE TABLE companies (
    company_id INT PRIMARY KEY,
    company_name VARCHAR(100),
    registration_number VARCHAR(50),
    tax_id VARCHAR(20),
    UNIQUE (registration_number),
    UNIQUE (tax_id)
);

-- 创建复合唯一约束
CREATE TABLE employee_projects (
    employee_id INT,
    project_id INT,
    role VARCHAR(50),
    UNIQUE (employee_id, project_id)
);

4.3 为现有表添加唯一约束

-- 为单列添加唯一约束
ALTER TABLE users
ADD UNIQUE (email);

-- 为多列添加唯一约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

4.4 删除唯一约束

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

5. 非空约束 (NOT NULL)

5.1 非空约束的特点

确保列中不能插入NULL值
强制要求提供数据
只能应用于列级别
不能通过约束名称引用

5.2 创建非空约束

-- 在创建表时定义非空约束
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)
);

5.3 为现有列添加非空约束

-- 为现有列添加非空约束
ALTER TABLE customers
MODIFY phone VARCHAR(20) NOT NULL;

5.4 删除非空约束

-- 删除非空约束
ALTER TABLE customers
MODIFY phone VARCHAR(20) NULL;

6. 检查约束 (CHECK)

6.1 检查约束的特点

限制列中值的范围
可以使用各种条件表达式
MySQL 8.0及以上版本完全支持
早期版本可能会解析但不强制执行

6.2 创建检查约束

-- 在创建表时定义检查约束（MySQL 8.0+）
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INT CHECK (stock >= 0)
);

-- 使用命名的检查约束
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2),
    age INT,
    CONSTRAINT chk_salary CHECK (salary > 0),
    CONSTRAINT chk_age CHECK (age >= 18 AND age < 65)
);

-- 创建复杂检查约束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    delivery_date DATE,
    status VARCHAR(20),
    CONSTRAINT chk_dates CHECK (delivery_date >= order_date),
    CONSTRAINT chk_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

6.3 为现有表添加检查约束

-- 为现有表添加检查约束
ALTER TABLE products
ADD CONSTRAINT chk_price CHECK (price > 0);

-- 为多个条件添加检查约束
ALTER TABLE employees
ADD CONSTRAINT chk_employee CHECK (hire_date <= CURRENT_DATE AND salary > 0);

6.4 删除检查约束

-- 删除检查约束
ALTER TABLE table_name
DROP CHECK constraint_name;

7. 默认约束 (DEFAULT)

7.1 默认约束的特点

当没有指定值时提供默认值
简化数据插入操作
确保常用值的一致性
可以使用常量值、函数或NULL

7.2 创建默认约束

-- 在创建表时定义默认约束
CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    is_published BOOLEAN DEFAULT FALSE,
    views INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'draft'
);

7.3 为现有列添加默认约束

-- 为现有列添加默认约束
ALTER TABLE articles
ALTER COLUMN status SET DEFAULT 'draft';

7.4 删除默认约束

-- 删除默认约束
ALTER TABLE articles
ALTER COLUMN status DROP DEFAULT;

8. 约束组合使用

8.1 综合运用约束

在实际应用中，通常需要组合使用多种约束来确保数据完整性：

-- 综合使用各种约束的例子
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL CHECK (total_amount > 0),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    shipping_address VARCHAR(200) NOT NULL,
    notes TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT,
    UNIQUE (customer_id, order_date)
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0),
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

8.2 约束的优先级和执行顺序

MySQL执行约束的顺序：

NOT NULL和数据类型检查
CHECK约束
外键约束
唯一约束和主键约束

9. 实际应用案例

9.1 用户和权限管理系统

-- 用户和权限管理系统数据库设计
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    CHECK (LENGTH(username) >= 3),
    CHECK (email LIKE '%@%.%')
);

CREATE TABLE roles (
    role_id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE permissions (
    permission_id INT PRIMARY KEY AUTO_INCREMENT,
    permission_name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by INT NOT NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(user_id)
);

CREATE TABLE role_permissions (
    role_id INT,
    permission_id INT,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE
);

9.2 电子商务产品目录

-- 电子商务产品目录数据库设计
CREATE TABLE categories (
    category_id INT PRIMARY KEY AUTO_INCREMENT,
    parent_category_id INT NULL,
    category_name VARCHAR(100) NOT NULL,
    description TEXT,
    display_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    UNIQUE (parent_category_id, category_name)
);

CREATE TABLE brands (
    brand_id INT PRIMARY KEY AUTO_INCREMENT,
    brand_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    website VARCHAR(255),
    country_of_origin VARCHAR(50),
    founded_year INT CHECK (founded_year > 1000 AND founded_year <= YEAR(CURRENT_DATE))
);

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    sku VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    brand_id INT,
    category_id INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    cost_price DECIMAL(10, 2) CHECK (cost_price > 0),
    discount_percent DECIMAL(5, 2) DEFAULT 0 CHECK (discount_percent >= 0 AND discount_percent <= 100),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    weight DECIMAL(8, 2) CHECK (weight > 0),
    dimensions VARCHAR(50),
    is_featured BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES brands(brand_id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE RESTRICT,
    CHECK (cost_price < price)
);

CREATE TABLE product_attributes (
    product_id INT,
    attribute_name VARCHAR(50),
    attribute_value VARCHAR(255) NOT NULL,
    PRIMARY KEY (product_id, attribute_name),
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE
);

CREATE TABLE product_images (
    image_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    alt_text VARCHAR(255),
    is_primary BOOLEAN DEFAULT FALSE,
    display_order INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
    UNIQUE (product_id, image_url)
);

9.3 医疗预约系统

-- 医疗预约系统数据库设计
CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(100) NOT NULL UNIQUE,
    location VARCHAR(100),
    phone VARCHAR(20)
);

CREATE TABLE doctors (
    doctor_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    specialization VARCHAR(100) NOT NULL,
    qualification VARCHAR(200) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (department_id) REFERENCES departments(department_id) ON DELETE RESTRICT
);

CREATE TABLE patients (
    patient_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE NOT NULL,
    gender ENUM('Male', 'Female', 'Other') NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) NOT NULL,
    address TEXT,
    emergency_contact VARCHAR(100),
    emergency_phone VARCHAR(20),
    blood_group VARCHAR(5),
    allergies TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CHECK (DATEDIFF(CURRENT_DATE, date_of_birth) >= 0)
);

CREATE TABLE appointments (
    appointment_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    appointment_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    status ENUM('Scheduled', 'Confirmed', 'Completed', 'Cancelled', 'No-show') DEFAULT 'Scheduled',
    reason_for_visit TEXT NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE RESTRICT,
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id) ON DELETE RESTRICT,
    UNIQUE (doctor_id, appointment_date, start_time),
    CHECK (end_time > start_time),
    CHECK (appointment_date >= CURRENT_DATE)
);

CREATE TABLE medical_records (
    record_id INT PRIMARY KEY AUTO_INCREMENT,
    patient_id INT NOT NULL,
    doctor_id INT NOT NULL,
    appointment_id INT,
    record_date DATE NOT NULL DEFAULT (CURRENT_DATE),
    diagnosis TEXT,
    treatment TEXT,
    prescription TEXT,
    follow_up_date DATE,
    notes TEXT,
    FOREIGN KEY (patient_id) REFERENCES patients(patient_id) ON DELETE RESTRICT,
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id) ON DELETE RESTRICT,
    FOREIGN KEY (appointment_id) REFERENCES appointments(appointment_id) ON DELETE SET NULL,
    CHECK (follow_up_date >= record_date)
);

10. 约束最佳实践

10.1 命名约定

采用一致的命名约定可以提高可维护性：

约束类型	命名前缀	示例
主键	pk_	pk_customer
外键	fk_	fk_order_customer
唯一约束	uq_	uq_email
检查约束	chk_	chk_positive_price
索引	idx_	idx_last_name

10.2 性能考量

约束对性能的影响：

约束检查需要额外的处理时间
外键约束在插入和更新时有额外开销
复杂的检查约束可能降低性能
非空约束和默认约束通常影响很小
合理使用约束通常比应用层检查更高效

10.3 可维护性建议

为所有约束命名以便于管理
记录约束的业务意义
尽可能在创建表时就添加约束
使用ALTER TABLE更改约束时要小心
定期检查约束的一致性和必要性

10.4 约束与数据迁移

处理数据迁移时的约束策略：

10.5 常见问题及解决方案

问题	解决方案
外键约束阻止删除	使用级联删除或先删除子表数据
违反唯一约束	查找并解决重复数据
添加非空约束失败	先更新现有NULL值，再添加约束
检查约束验证失败	识别并更新不符合条件的数据
外键性能问题	确保引用列已建立索引

通过合理使用约束，可以在数据库层面确保数据的完整性和一致性，减少应用程序中的验证代码，并防止错误数据进入系统。虽然约束会带来一定的性能开销，但其提供的数据质量保证通常是值得的。在设计数据库时，应根据业务需求和性能要求，权衡选择适当的约束策略。

结语
感谢您的阅读！期待您的一键三连！欢迎指正！

在这里插入图片描述