一文详解MySQL表数据完整性的8大约束机制

sql
-- 创建表时指定非空约束
create table users (
    id int primary key,
    username varchar(50) not null,
    email varchar(100) not null,
    phone varchar(20)  -- 允许为空
);

-- 修改表添加非空约束
alter table users modify phone varchar(20) not null;

-- 插入数据验证
insert into users (id, username, email) 
values (1, '张三', 'zhangsan@example.com'); -- 成功

insert into users (id, username, email) 
values (2, null, 'lisi@example.com'); -- 失败：username不能为null
最佳实践：

业务关键字段如用户名、邮箱等应设置not null约束

对于可选信息字段，可根据业务需求决定是否允许null

sql
-- 单字段主键
create table products (
    product_id int primary key,
    product_name varchar(100) not null,
    price decimal(10, 2)
);

-- 复合主键
create table order_items (
    order_id int,
    product_id int,
    quantity int,
    primary key (order_id, product_id)
);

-- 添加主键约束
alter table employees add primary key (emp_id);

-- 删除主键约束
alter table employees drop primary key;
特点：

每个表只能有一个主键

主键值必须唯一且不为null

主键自动创建索引，提高查询效率

sql
-- 创建外键约束
create table departments (
    dept_id int primary key,
    dept_name varchar(50) not null
);

create table employees (
    emp_id int primary key,
    emp_name varchar(50) not null,
    dept_id int,
    foreign key (dept_id) references departments(dept_id)
    on delete cascade   -- 级联删除
    on update cascade   -- 级联更新
);

-- 添加外键约束
alter table orders 
add constraint fk_customer
foreign key (customer_id) references customers(customer_id)
on delete set null;  -- 设置为null

-- 外键操作选项：
-- cascade: 级联操作
-- set null: 设置为null
-- restrict/no action: 限制操作
-- set default: 设置为默认值
外键使用场景：

一对多关系（部门与员工）

多对多关系（通过中间表实现）

确保引用数据的存在性

sql
-- 创建检查约束
create table students (
    student_id int primary key,
    student_name varchar(50) not null,
    age int,
    gender char(1),
    score decimal(5, 2),
    -- 单字段检查约束
    constraint chk_age check (age >= 18 and age <= 60),
    -- 多字段检查约束
    constraint chk_gender check (gender in ('m', 'f')),
    constraint chk_score check (score >= 0 and score <= 100)
);

-- 添加检查约束
alter table employees 
add constraint chk_salary 
check (salary >= 3000 and salary <= 100000);

-- 复杂的检查约束
create table orders (
    order_id int primary key,
    order_date date,
    delivery_date date,
    constraint chk_dates check (delivery_date >= order_date)
);

sql
-- 创建唯一约束
create table users (
    user_id int primary key,
    username varchar(50) unique,  -- 列级约束
    email varchar(100),
    phone varchar(20),
    -- 表级约束
    constraint uq_email unique (email),
    -- 复合唯一约束
    constraint uq_user_contact unique (username, phone)
);

-- 添加唯一约束
alter table products 
add constraint uq_product_code unique (product_code);

-- 删除唯一约束
alter table users drop index uq_email;
与主键的区别：

唯一约束允许null值，主键不允许

一个表可以有多个唯一约束，但只有一个主键

唯一约束不自动创建聚集索引

sql
-- 使用自增约束
create table orders (
    order_id int primary key auto_increment,
    order_number varchar(20) unique,
    customer_id int,
    order_date timestamp default current_timestamp,
    total_amount decimal(10, 2)
) auto_increment = 1000;  -- 设置起始值

-- 插入数据
insert into orders (order_number, customer_id, total_amount)
values ('ord2023001', 101, 299.99);  -- order_id自动生成

-- 查看当前自增值
select auto_increment 
from information_schema.tables 
where table_schema = 'your_database' 
and table_name = 'orders';

-- 修改自增起始值
alter table orders auto_increment = 2000;
注意事项：

通常与主键结合使用

只能用于整数类型（int, bigint等）

删除记录不会重置自增值

sql
-- 使用无符号约束
create table inventory (
    item_id int primary key auto_increment,
    item_name varchar(100) not null,
    quantity int unsigned not null default 0,  -- 数量不能为负数
    price decimal(10, 2) unsigned,            -- 价格不能为负数
    weight float unsigned                     -- 重量不能为负数
);

-- 范围对比
-- tinyint: -128 ~ 127 (有符号) / 0 ~ 255 (无符号)
-- int: -2147483648 ~ 2147483647 (有符号) / 0 ~ 4294967295 (无符号)
适用场景：

年龄、数量、价格等不可能为负值的字段

需要更大正数范围的场景

sql
-- 创建默认约束
create table employees (
    emp_id int primary key auto_increment,
    emp_name varchar(50) not null,
    hire_date date default (current_date),  -- 当前日期
    status varchar(20) default 'active',
    created_at timestamp default current_timestamp,
    updated_at timestamp default current_timestamp on update current_timestamp,
    department varchar(50) default 'general'
);

-- 添加默认约束
alter table products 
alter column stock_quantity set default 0;

-- 移除默认约束
alter table products 
alter column stock_quantity drop default;

-- 使用默认值插入数据
insert into employees (emp_name) 
values ('李四');  -- 其他字段使用默认值

sql
-- 创建客户表
create table customers (
    customer_id int primary key auto_increment,
    customer_code varchar(20) unique not null,
    customer_name varchar(100) not null,
    email varchar(100) unique not null,
    phone varchar(20) unique,
    status enum('active', 'inactive', 'suspended') default 'active',
    created_at timestamp default current_timestamp,
    constraint chk_email check (email like '%@%')
) auto_increment = 1000;

-- 创建产品表
create table products (
    product_id int primary key auto_increment,
    product_code varchar(20) unique not null,
    product_name varchar(200) not null,
    category_id int,
    price decimal(10, 2) unsigned not null,
    stock_quantity int unsigned default 0,
    is_available boolean default true,
    created_at timestamp default current_timestamp,
    constraint chk_price check (price > 0),
    constraint chk_stock check (stock_quantity >= 0)
);

-- 创建订单表
create table orders (
    order_id int primary key auto_increment,
    order_number varchar(30) unique not null,
    customer_id int not null,
    order_date date default (current_date),
    total_amount decimal(12, 2) unsigned default 0.00,
    status enum('pending', 'processing', 'shipped', 'delivered', 'cancelled') default 'pending',
    created_at timestamp default current_timestamp,
    foreign key (customer_id) references customers(customer_id)
    on delete restrict
    on update cascade
);

-- 创建订单明细表
create table order_details (
    order_detail_id int primary key auto_increment,
    order_id int not null,
    product_id int not null,
    quantity int unsigned not null default 1,
    unit_price decimal(10, 2) unsigned not null,
    subtotal decimal(12, 2) unsigned generated always as (quantity * unit_price) stored,
    foreign key (order_id) references orders(order_id) on delete cascade,
    foreign key (product_id) references products(product_id) on delete restrict,
    constraint chk_quantity check (quantity > 0),
    constraint uq_order_product unique (order_id, product_id)
);