MySQL约束

MySQL 约束是用于强制表中数据规则的机制，它们确保数据的准确性和可靠性。如果存在违反约束的数据操作，MySQL 会阻止该操作。

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识,要求非空且唯一	PRIMARY KEY
默认约束	保存数据时,如果未指定该字段的值,则采用默认值	DEFAULT
检查约束(8.0 .16版本之	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接,保证数据的一致 性和完整性	FOREIGN K

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

1、PRIMARY KEY 约束

• 唯一标识表中的每一行
• 值不能为NULL
• 每个表只能有一个主键
• 自动创建唯一索引

示例

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

-- 创建表时定义多列复合主键
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 使用ALTER TABLE添加主键
CREATE TABLE employees (
    emp_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

ALTER TABLE employees ADD PRIMARY KEY (emp_id);

-- 使用CONSTRAINT命名主键
CREATE TABLE customers (
    cust_id INT,
    cust_name VARCHAR(100),
    CONSTRAINT pk_customers PRIMARY KEY (cust_id)
);

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

2、FOREIGN KEY 约束

• 维护表间的引用完整性
• 确保子表值匹配父表主键
• 支持级联操作（删除/更新）

示例

-- 创建表时定义外键
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(cust_id)
);

-- 命名外键并指定操作
CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    CONSTRAINT fk_order
        FOREIGN KEY (order_id) 
        REFERENCES orders(order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- 使用ALTER TABLE添加外键
CREATE TABLE payments (
    payment_id INT PRIMARY KEY,
    order_id INT,
    amount DECIMAL(10,2)
);

ALTER TABLE payments ADD CONSTRAINT fk_payment_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE SET NULL;

-- 删除外键
ALTER TABLE payments DROP FOREIGN KEY fk_payment_order;

外键操作选项

• ON DELETE CASCADE: 当父表中的记录被删除时，自动删除子表中的相关记录
• ON DELETE SET NULL: 当父表中的记录被删除时，将子表中的外键设置为 NULL
• ON DELETE RESTRICT: 阻止删除父表中的记录（默认）
• ON UPDATE CASCADE: 当父表中的主键更新时，自动更新子表中的外键

3、UNIQUE 约束

• 确保列值唯一
• 允许NULL值（多个NULL视为不同值）
• 可为多列组合
• 自动创建唯一索引

示例

-- 创建表时定义唯一约束
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_code VARCHAR(20) UNIQUE,
    product_name VARCHAR(100) NOT NULL
);

-- 定义多列复合唯一约束
CREATE TABLE user_emails (
    user_id INT,
    email_type VARCHAR(20),
    email_address VARCHAR(100),
    UNIQUE (user_id, email_type)
);

-- 命名唯一约束
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100),
    CONSTRAINT uq_dept_name UNIQUE (dept_name)
);

-- 使用ALTER TABLE添加唯一约束
ALTER TABLE employees ADD CONSTRAINT uq_employee_email UNIQUE (email);

-- 删除唯一约束
ALTER TABLE employees DROP INDEX uq_employee_email;

4、NOT NULL 约束

• 强制列不接受NULL值
• 确保关键数据完整性
• 常与其他约束组合使用
  示例

-- 创建表时定义NOT NULL约束
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL,
    phone VARCHAR(15) NULL -- 明确指定可为NULL
);

-- 使用ALTER TABLE添加NOT NULL约束
ALTER TABLE students MODIFY COLUMN phone VARCHAR(15) NOT NULL;

-- 移除NOT NULL约束
ALTER TABLE students MODIFY COLUMN phone VARCHAR(15) NULL;

5、CHECK 约束 (MySQL 8.0.16+)

• 自定义数据验证规则
• 使用布尔表达式
• 可命名便于管理
  示例

-- 创建表时定义CHECK约束
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2) CHECK (salary > 0),
    age INT CHECK (age >= 18 AND age <= 65)
);

-- 命名CHECK约束
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2),
    stock_quantity INT,
    CONSTRAINT chk_price CHECK (price >= 0),
    CONSTRAINT chk_stock CHECK (stock_quantity >= 0)
);

-- 使用ALTER TABLE添加CHECK约束
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- 删除CHECK约束
ALTER TABLE employees DROP CHECK chk_salary;

6、DEFAULT 约束

• 指定列默认值
• 插入时未提供值则使用默认值
• 常与时间戳、状态码等配合
  示例

-- 创建表时定义DEFAULT约束
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE DEFAULT (CURRENT_DATE),
    status VARCHAR(20) DEFAULT 'Pending',
    total_amount DECIMAL(10,2) DEFAULT 0.00
);

-- 使用ALTER TABLE添加DEFAULT约束
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'Processing';

-- 移除DEFAULT约束
ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;