MySQL约束

约束(constraint)概述

为什么要约束

为了保证数据完整性

什么是约束

对表中字段的（强制）限制

约束的分类

• 角度一：字段个数

单类约束，多列约束

• 角度二：约束的作用范围 / 定义位置

列级约束：将此约束声明在字段的后面

表级约束：在表中所有字段声明完，在所有字段的后面声明的约束

• 约束的作用（功能）

not null (非空约束)

unique (唯一性约束)

primary key (主键约束)

foreign key (外键约束)

check (检查约束)

default (默认值约束)

如何添加约束

CREATE TABLE 添加约束
ALTER TABLE 增加、删除约束

如何查看表中约束

select * from information_schema.table_constraints
where table_name ="表的名字";

NOT NULL (非空约束)

限定某个字段 / 某列的值不能为空

特点

• 默认所有类型的值都可以是NULL，包括INT,FLOAT等
• 非空约束只能出现在表对象的列上，只能某个列单独非空，不能组合非空（只能列级约束）
• 一个表可以有很多列都分别设置非空
• 字符串" "不等于NULL；0也不等于NULL

添加非空约束

CREATE TABLE 添加约束

CREATE TABLE emp2(           #建议大写
id int not null,
name varchar(15) not null,
email varchar(25),
salary decimal(10,2)
);
desc emp2;

#正确
insert into emp2(id,name,email,salary)
values(1,"Tom","tom@1126.com",3400);
#错误
insert into emp2(id,name,email,salary)
values(null,"Tom","tom@1126.com",3400);
insert into emp2(id,name,email,salary)
values(1,null,"tom@1126.com",3400);

ALTER TABLE 增加约束

#增加
alter table emp2
modify email varchar(25) not null;

删除非空约束

#删除
alter table emp2
modify email varchar(25) null;

注：若在之前已经让要修改的字段中有null，则要先将null去除

UNIQUE (唯一性约束)

用来限制某个字段 / 某列的值不能重复

特点

• 同一个表可以有多个约束
• 唯一约束是以某一列的值唯一，也可以是多个列组合的值唯一
• 唯一性约束允许列值为空(可以多次添加null)
• 在创建唯一性约束时，如果不给唯一约束命名，则默认和列同名（若是多列，则和第一个列名相同）

添加唯一约束

单列

CREATE TABLE 添加约束

CREATE TABLE emp3(
id int unique,
name varchar(15),
email varchar(25),
salary decimal(10,2),
#表级约束
constraint uk_emp3_email unique (email)
           #约束名的名字
);
desc emp3;

#正确
insert into emp3(id,name,email,salary)
values(1,"Tom","tom@126.com",4500);
insert into emp3(id,name,email,salary)
values(2,"Tom1",null,4700);
insert into emp3(id,name,email,salary)
values(3,"Tom2",null,4600);
#错误
insert into emp3(id,name,email,salary)
values(1,"Tom3","to1@126.com",4500); #id重复

ALTER TABLE 增加约束

#添加
方式一：
alter table emp3
add constraint uk_emp3_sal unique (salary);#和表级约束差一样
                #约束的名字
方式二：
alter table emp3
modify name varchar(15) unique;#和列级约束一样

多列（复合型唯一性约束）

约束后的字段中的数据，不能都是一样，只要有一点不同，就可以执行

CREATE TABLE USER(
id int ,
name varchar(15),
·password· varchar(25),
#表级约束
constraint uk_USER_name_pwd unique (name,`password`)
           #约束名的名字      
);
#正确
insert into USER
values (1,"Tom","abc");
insert into USER
values (1,"Tom","abcd");
#错误
insert into USER
values (1,"Tom","abc"); #

删除唯一性约束

• 添加唯一约束的列上会自动创建唯一索引
• 删除唯一约束只能通过删除唯一索引的方式删除
• 删除时需要指明唯一索引名，唯一索引名和唯一约束名一样（见唯一约束的特点）

#删除
alter table emp3
drop index 索引名；

PRIMARY KEY（主键约束）

用来唯一标识表中的一行记录（区分不同的行）

特点

• 主键约束相当于非空约束+唯一约束，主键约束列不可以重复和出现空值
• 一个表最多一个主键约束（可以列级，也可以表级）
• 如果是多列约束，这些列也是不能有空值的，且组合的值不可以重复
• MySQL的主键名是PRIMARY, 就算自己命名了主键名也没用
• 创建主键约束时，系统会建立对应的主键索引（主键查询，效率高）
• 不要修改主键字段的值（主键是数据记录的唯一标识），可能会破坏数据的完整性

添加主键约束

单列

CREATE TABLE 添加约束

CREATE TABLE emp4(
id int primary key,#列级约束
name varchar(15),
email varchar(25),
salary decimal(10,2)
);

CREATE TABLE emp5(
id int,
name varchar(15),
email varchar(25),
salary decimal(10,2),
constraint pk_emp5_id primary key (id)#表级约束，但是没有必要起名字
);

#正确
insert into emp4(id,name,email,salary)
values(1,"Tom","tom@1126.com",3400);
#错误
insert into emp4(id,name,email,salary)
values(1,"Tom","tom@1126.com",3400); #id重复
insert into emp4(id,name,email,salary)
values(null,"Tom","tom@1126.com",3400); #出现null值

ALTER TABLE 增加约束

ALTER TABLE emp6
add primary key (id);

多列

CREATE TABLE USER1(
id int ,
name varchar(15),
·password· varchar(25),
primary key (name,`password`)
);

删除主键约束(实际开发中不会删除)

alter table emp6
drop primary key;

自增列：AUTO_INCREMENT

让某个字段的值自增

特点和要求

• 一个表只能有一个自增长列
• 自增列必须是键列（主键列，唯一键列）
• 自增约束的列类型必须是整型
• 如果自增列指定了0和null，会在当前最大值上自增；如果自增列手动指定了具体值，直接赋值具体值

注：在实际开发中如果主键作用的字段有auto_increment，则在添加数据是不需要对该字段去赋值

添加自增长列

CREATE TABLE 添加约束

CREATE TABLE emp7(
id int primary key auto_increment ,
name varchar(15)
);

insert into emp7(name)
values("Tom"); #执行一次，id+1不需要赋值

ALTER TABLE 增加约束（一般不会使用）

alter table emp8 #id已经是主键
modify id int auto_increment;

删除自增列

alter table emp8
modify id int;

MySQL8.0新特性——自增量的持久性

若删除一行记录，重启服务器，再执行添加语句，则该自增列的值=刚才删除的行的自增列的数+1

FOREIGN KEY(外键约束)

限定某个表的某个字段的引用完整性

不可以跨存储引擎

主表和从表 / 父表和子表

• 主表（父表）：被引用的表，被参与的表
• 从表（子表）：引用别人的表，参考别人的表

eg:学生表和课程表是主表，选课表是从表

特点：

• 从表的外键列，必须是主表的主键列或是唯一约束列（被参考的数要是唯一的）
• 创建外键约束时，如果不起名字，默认名不是列名，而是自动产生一个外键名
• 创建外键时，要先创建主表，再创建从表
• 删除时，先删除从表（或是外键约束），再删除主表（要是删除其中数据道理也是一样）
• 在从表中建立外键约束，一个表也可建立多个外键约束
• 从表的外键列和主表的参照列名字可以不同，但是数据类型必须是一样的
• 当创建外键约束时，系统默认在所在的列上建立对应的普通索引，但是索引名是列名，不是外键的约束名
• 删除外键后，必须手动删除对应的索引

添加外键约束

CREATE TABLE 添加约束

#先创建主表
CREATE TABLE dept1(
dept_id int primary key,#必须给要关联的列添加主键或是唯一约束
dept_name varchar(15)
);
#创建从表
CREATE TABLE emp01(
emp01_id int primary key auto_increment,
emp01_name varchar(15),
department_id int,
#表级约束
constraint fk_emp01_id foreign key(department_id) references dept1(dept_id)
);

#演示外键效果
insert into dept1
values (10,"IT");#先创建主表的数据10号部门

INSERT INTO emp01
values (1001,"Tom",10);
#若是删除或是更改当前主表中的10号部门，是不可行的

ALTER TABLE 增加约束

#主表
CREATE TABLE dept2(
dept_id int primary key,
dept_name varchar(15)
);
#从表
CREATE TABLE emp02(
emp01_id int primary key auto_increment,
emp01_name varchar(15),
department_id int,
);

alter table emp02
add constraint fk_emp02_deot_id foreign key (department_id) references dept2(dept_id);

约束等级

最好采用ON UPDATE CASADE ON DELETE RESTRICT 的方式，声明位置在约束条件的后面

删除约束

1. 先查看约束名和删除外键约束

select * from information_schema.table_constraints
where table_name ="表的名字";

alter table 从表名 
drop foreign key 约束名;

2. 查看索引名，并删除（只能手动）

show index from 表名;

alter table 从表名
drop index 索引名;

开发场景

外键会让操作受到限制

不建议使用外键和级联，一切外键概念必须在应用层解决（经典白学）

CHECK (检查约束)

检查某个字段的值是否符合相应的要求，一般是指值的范围

MySQL5.7不支持CHECK约束

CREATE TABLE emp10(
id int,
name varchar(15),
salary decimal(10,2) check (salary > 2000)  
);

insert into emp10
values (1,"Tom",3000);#成功
insert into emp10
values (2,"Tom1",1500);#失败

DEFAULT(默认值约束)

给某个字段 / 某列指定一个默认值，一旦指定以后，在插入数据时，如果此字段没有显示赋值，则赋值为默认值

添加默认值约束

CREATE TABLE 添加约束

CREATE TABLE emp11(
id int,
name varchar(15),
salary decimal(10,2) default 2000
);

insert into emp11
values (1,"Tom",3000);#成功
insert into emp11(id,name)
values (2,"Tom1");#默认添加2000的工资

ALTER TABLE 增加约束

ALTER TABLE emp12
modify salary varchar(8,2) default 2500;

删除默认值约束

ALTER TABLE emp12
modify salary varchar(8,2)；#直接去掉（NOT NULL差不多）

原文地址：https://www.cnblogs.com/wht-de-bk/archive/2022/03/14/16006222.html