mysql中的CONSTRAINT关键词的用法

在 MySQL 中，CONSTRAINT 关键字用于定义数据完整性约束，确保表中数据符合特定规则。通过 CONSTRAINT，你可以显式命名约束，使其更易管理和维护。以下是详细用法和示例：

一、CONSTRAINT 的作用

  1. 显式命名约束：为约束规则指定名称，方便后续修改或删除。
  2. 数据完整性：强制数据满足业务规则（如唯一性、关联性、范围限制等）。
  3. 统一管理：在多列约束或跨表关联时，命名约束更清晰。

二、核心约束类型及用法

1. 主键约束（PRIMARY KEY）

  • 作用：唯一标识每行数据，不允许重复或 NULL。
  • 语法： 
    CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    CONSTRAINT 约束名 PRIMARY KEY (列1, 列2) -- 复合主键
);
  • 示例： 
    CREATE TABLE students (
    student_id INT,
    course_id INT,
    grade DECIMAL(5,2),
    CONSTRAINT pk_student_course 
        PRIMARY KEY (student_id, course_id) -- 复合主键
);

2. 外键约束（FOREIGN KEY）

  • 作用：确保两表之间的数据关联有效。
  • 语法： 
    CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    CONSTRAINT 约束名 FOREIGN KEY (外键列)
        REFERENCES 主表名 (主表列)
        ON DELETE 级联操作
        ON UPDATE 级联操作
);
  • 示例： 
    CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    CONSTRAINT fk_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE   -- 主表删除时，级联删除从表数据
        ON UPDATE SET NULL -- 主表更新时，从表外键设为 NULL
);

3. 唯一约束（UNIQUE）

  • 作用：确保某列（或列组合）的值唯一，允许 NULL。
  • 语法： 
    CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    CONSTRAINT 约束名 UNIQUE (列1, 列2) -- 复合唯一约束
);
  • 示例： 
    CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    email VARCHAR(100),
    CONSTRAINT uc_email 
        UNIQUE (email) -- 确保邮箱唯一
);

4. 检查约束（CHECK）

  • 作用：限制列值必须满足条件（MySQL 8.0+ 支持）。
  • 语法： 
    CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    CONSTRAINT 约束名 CHECK (条件)
);
  • 示例： 
    CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2),
    CONSTRAINT chk_price 
        CHECK (price > 0) -- 价格必须大于 0
);

5. 默认值（DEFAULT）

  • 作用：插入数据时，未指定列值时使用默认值。
  • 语法： 
    CREATE TABLE 表名 (
    列1 数据类型 DEFAULT 默认值
);
  • 示例： 
    CREATE TABLE orders (
    order_date DATE DEFAULT CURRENT_DATE -- 默认当前日期
);

三、CONSTRAINT 的管理操作

1. 添加约束

  • 添加主键： 
    ALTER TABLE students
ADD CONSTRAINT pk_student 
    PRIMARY KEY (student_id);
  • 添加外键： 
    ALTER TABLE orders
ADD CONSTRAINT fk_user
    FOREIGN KEY (user_id) 
    REFERENCES users(user_id);

2. 删除约束

  • 删除主键： 
    ALTER TABLE students
DROP PRIMARY KEY; -- 主键无需指定名称
  • 删除外键： 
    ALTER TABLE orders
DROP FOREIGN KEY fk_user; -- 需指定外键名称

四、实际应用场景

场景 1：防止重复数据

-- 确保书名和作者组合唯一
CREATE TABLE books (
    isbn VARCHAR(13) PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(50),
    CONSTRAINT uc_title_author 
        UNIQUE (title, author)
);

场景 2：级联删除关联数据

-- 删除用户时，自动删除其订单
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    CONSTRAINT fk_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE
);

场景 3：强制业务规则

-- 确保员工年龄 ≥ 18 岁
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    age INT,
    CONSTRAINT chk_age 
        CHECK (age >= 18)
);

五、注意事项

  1. 命名规范：使用 pk_表名、fk_表名_列名 等格式，增强可读性。
  2. 存储引擎：外键约束需使用 InnoDB，MyISAM 不支持。
  3. 性能影响：过多的约束可能降低写入性能。
  4. 版本兼容性：CHECK 约束仅 MySQL 8.0+ 完全支持。

六、完整示例

-- 主表：部门
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);

-- 从表：员工（含外键和检查约束）
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    salary DECIMAL(10,2),
    CONSTRAINT fk_dept 
        FOREIGN KEY (dept_id) 
        REFERENCES departments(dept_id)
        ON DELETE SET NULL,
    CONSTRAINT chk_salary 
        CHECK (salary >= 3000)
);

通过合理使用 CONSTRAINT，可以显著提升数据的准确性和一致性，是数据库设计的核心技能之一。

以下是 15道关于 MySQL CONSTRAINT 的练习题，涵盖主键、外键、唯一约束、检查约束等核心用法，帮助你巩固知识并熟练使用约束规则：

基础练习（1-5）

  1. 创建表时定义主键约束
    创建一个 students 表，包含 student_id（主键）、name、age，并为 student_id 显式命名主键约束为 pk_student。
CREATE TABLE students (
    student_id INT,
    name VARCHAR(50),
    age INT,
    CONSTRAINT pk_student PRIMARY KEY (student_id)
);

  1. 添加复合主键约束
    创建一个 course_selection 表，包含 student_id 和 course_id，要求二者的组合为主键，约束命名为 pk_student_course。

  2. 定义唯一约束
    在 employees 表中，为 email 列添加唯一约束，命名为 uc_email，确保邮箱唯一。

  3. 创建外键约束
    在 orders 表中添加 user_id 列，并创建外键约束 fk_user，使其引用 users 表的 user_id 列，级联删除从表数据。

  4. 检查约束限制数值范围
    创建一个 products 表，包含 price 列，要求价格必须大于 0，约束命名为 chk_price。

进阶练习（6-10）

  1. 多列唯一约束
    在 books 表中，为 title 和 author 列添加唯一约束 uc_title_author，确保书名和作者组合唯一。

  2. 级联更新外键
    修改 orders 表的外键约束 fk_user，使其在主表 users 的 user_id 更新时，从表的 user_id 自动更新。

  3. 删除主键约束
    删除 students 表的主键约束 pk_student，然后重新添加一个新的主键约束到 student_id 列。

  4. 检查约束限制日期
    在 events 表中，添加约束 chk_event_date，确保 start_date 必须早于 end_date。

  5. 默认值约束
    在 orders 表中，为 order_date 列设置默认值为当前日期，约束命名为 df_order_date。

实战场景（11-15）

  1. 复合外键约束
    创建一个 order_details 表，包含 order_id 和 product_id，并添加外键约束 fk_order_product，使其同时引用 orders 表的 order_id 和 products 表的 product_id。

  2. 检查约束限制年龄
    在 users 表中，添加约束 chk_age，确保 age 列的值在 18 到 100 之间。

  3. 删除外键约束
    从 orders 表中删除外键约束 fk_user，然后重新添加一个允许主表更新时从表设为 NULL 的外键约束。

  4. 自增主键约束
    在 employees 表中，定义 emp_id 为自增主键，约束命名为 pk_emp。

  5. 检查约束限制字符串格式
    在 users 表中，添加约束 chk_phone，确保 phone 列为 11 位数字（模拟手机号格式）。

附加挑战

  • 场景 1：在 employees 表中，添加约束 chk_salary，确保 salary 必须大于等于 3000，且 department_id 必须存在于 departments 表中。
  • 场景 2：创建一个 projects 表，包含 project_id（主键）、start_date、end_date，并添加约束确保 end_date 必须比 start_date 晚至少 7 天。

练习要求

  1. 每个练习需写出完整的 SQL 语句。
  2. 显式命名所有约束（如 pk_xxx、fk_xxx）。
  3. 验证约束效果（如尝试插入违反约束的数据，观察是否报错）。

通过完成这些练习，你将熟练掌握 CONSTRAINT 的核心用法，并能在实际项目中灵活应用约束规则！

基础练习答案

1. 创建表时定义主键约束

2. 添加复合主键约束

CREATE TABLE course_selection (
    student_id INT,
    course_id INT,
    grade DECIMAL(5,2),
    CONSTRAINT pk_student_course PRIMARY KEY (student_id, course_id)
);

3. 定义唯一约束

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    email VARCHAR(100),
    CONSTRAINT uc_email UNIQUE (email)
);

4. 创建外键约束（级联删除）

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    CONSTRAINT fk_user 
        FOREIGN KEY (user_id) 
        REFERENCES users(user_id)
        ON DELETE CASCADE
);

5. 检查约束限制数值范围

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2),
    CONSTRAINT chk_price CHECK (price > 0)
);

进阶练习答案

6. 多列唯一约束

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(50),
    CONSTRAINT uc_title_author UNIQUE (title, author)
);

7. 级联更新外键

ALTER TABLE orders 
ADD CONSTRAINT fk_user 
    FOREIGN KEY (user_id) 
    REFERENCES users(user_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;  -- 级联更新

8. 删除并重新添加主键约束

-- 删除主键
ALTER TABLE students DROP PRIMARY KEY;

-- 重新添加主键
ALTER TABLE students 
ADD CONSTRAINT pk_student_new PRIMARY KEY (student_id);

9. 检查约束限制日期范围

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    CONSTRAINT chk_event_date CHECK (start_date < end_date)
);

10. 默认值约束

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE DEFAULT (CURRENT_DATE)
);

实战场景答案

11. 复合外键约束

CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    CONSTRAINT fk_order_product 
        FOREIGN KEY (order_id, product_id) 
        REFERENCES orders(order_id, product_id)  -- 假设 orders 和 products 有复合主键
);

12. 检查约束限制年龄范围

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    age INT,
    CONSTRAINT chk_age CHECK (age BETWEEN 18 AND 100)
);

13. 删除并重新添加外键约束

-- 删除外键
ALTER TABLE orders DROP FOREIGN KEY fk_user;

-- 重新添加外键（允许更新时设为 NULL）
ALTER TABLE orders 
ADD CONSTRAINT fk_user_new 
    FOREIGN KEY (user_id) 
    REFERENCES users(user_id)
    ON DELETE SET NULL
    ON UPDATE SET NULL;

14. 自增主键约束

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(50),
    CONSTRAINT pk_emp PRIMARY KEY (emp_id)
);

15. 检查约束限制手机号格式

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    phone VARCHAR(11),
    CONSTRAINT chk_phone CHECK (phone REGEXP '^[0-9]{11}$')
);

附加挑战答案

挑战 1：检查薪资与部门关联

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    salary DECIMAL(10,2),
    department_id INT,
    CONSTRAINT chk_salary CHECK (salary >= 3000),
    CONSTRAINT fk_dept 
        FOREIGN KEY (department_id) 
        REFERENCES departments(dept_id)
);

挑战 2：检查项目日期间隔

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    start_date DATE,
    end_date DATE,
    CONSTRAINT chk_project_dates 
        CHECK (DATEDIFF(end_date, start_date) >= 7)
);

验证方法

  1. 插入合法数据：

    INSERT INTO products (product_id, price) VALUES (1, 10.5);  -- 成功

  2. 插入非法数据：

    INSERT INTO products (product_id, price) VALUES (2, -5);  -- 失败，违反 chk_price

  3. 查看约束信息：

    SHOW CREATE TABLE products;  -- 显示所有约束定义

通过以上练习，你可以熟练掌握 CONSTRAINT 的用法，并能在实际项目中灵活应用！

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。