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等
• 非空约束只能出现在表对象的列上，只有一列不为空，不能与非空组合(仅列级约束)
  
  [En]

  non-empty constraints can only appear on the columns of table objects, only a column * is not empty * , and cannot be combined with non-empty (column-level constraints only)*

• 一个表可以有多个列分别设置为非空
  
  [En]

  A table can have many columns set to non-empty respectively*

• 字符串’ ‘不等于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 (唯一性约束)

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

特点

• 同一个表可以有多个约束
• 唯一约束是一列的值是唯一的，或者多列组合的值是唯一的
  
  [En]

  the unique constraint is that the value of a column is unique, or the value of a combination of multiple columns is unique*

• 唯一性约束允许列值为空(可以多次添加null)
• 创建唯一性约束时，如果不命名唯一约束，则默认为与该列同名(如果有多列，则与第一个列名相同)
  
  [En]

  when creating a uniqueness constraint, if you do not name the unique constraint, the default is the same name as the column (if there are multiple columns, the same as the first column name)*

添加唯一约束

单列

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'); #

删除唯一性约束

• 在添加唯一约束的列上自动创建唯一索引
  
  [En]

  unique indexes are automatically created on columns that add unique constraints*

• 删除唯一约束 只能通过删除唯一索引的方式删除
• 删除时需要指定唯一索引名，与唯一约束名称相同(参见唯一约束特征)
  
  [En]

  when deleting, you need to specify a unique index name, which is the same as the unique constraint name (see unique constraint characteristics)*

#删除alter table emp3drop index 索引名；

PRIMARY KEY（主键约束）

用于唯一标识表中的一行记录(区分不同的行)

特点

• 主键约束相当于 非空约束+唯一约束，主键约束列不可以重复和出现空值
• 一个表最多 一个主键约束（可以列级，也可以表级）
• 如果是多列约束，则这些列不能有空值，组合后的值不能重复。
  
  [En]

  if it is a multi-column constraint, these columns cannot have null values, and the combined values cannot be repeated.*

• MySQL的主键名是PRIMARY, 就算自己命名了主键名也没用
• 创建主键约束时，系统建立对应的主键索引(主键查询，高效)
  
  [En]

  when creating a primary key constraint, the system establishes the corresponding primary key index (primary key query, efficient)*

• 不要修改主键字段的值（主键是数据记录的唯一标识），可能会破坏数据的完整性

添加主键约束

单列

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

让某个字段的值自增

特点和要求

• 一个表只能有一个自增长列
  
  [En]

  A table can have only one self-growing column*

• 自增列必须是键列(主键列、唯一键列)
  
  [En]

  self-incrementing column must be a key column (primary key column, unique key column)*

• 自增约束的列类型必须为整数
  
  [En]

  the column type of the self-increasing constraint must be an integer*

• 如果自增列指定了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(外键约束)

限制表中字段的引用完整性

不可以跨存储引擎

主表和从表 / 父表和子表

• 主表(父表)：被引用表、参与表
  
  [En]

  Master table (parent table): referenced table, participating table*

• 来自表(子表)：引用他人的表，引用他人的表
  
  [En]

  from tables (child tables): refer to other people’s tables and refer to other people’s tables*

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

特点：

• 备表的外键列必须是主表的主键列或唯一约束列(引用的数字必须唯一)
  
  [En]

  the foreign key column of the slave table must be the primary key column of the master table or the unique constraint column (the number referenced must be unique)*

• 创建外键约束时，如果没有名称，默认名称不是列名，而是自动生成外键名称
  
  [En]

  when creating a foreign key constraint, if there is no name, * the default name is not a column name, but a foreign key name is automatically generated * *

• 创建外键时，先创建主表，再创建从表
  
  [En]

  when creating a foreign key, first create a master table, and then create a slave table*

• 删除时，先删除备表(或外键约束)，再删除主表(删除数据也是如此)
  
  [En]

  when deleting, delete the slave table (or foreign key constraint) first, and then delete the master table (the same is true if the data is deleted)*

• 在子表中创建外键约束，一张表中也可以建立多个外键约束
  
  [En]

  create foreign key constraints in a slave table, and multiple foreign key constraints can also be established in a table*

• 备表的外键列可以与主表的引用列不同，但数据类型必须相同
  
  [En]

  the foreign key column of the slave table can be different from the reference column of the master table, but * the data type must be the same * *

• 创建外键约束时，系统默认在列上建立对应的普通索引，但索引名是列名，而不是外键的约束名。
  
  [En]

  when creating a foreign key constraint, the system establishes a corresponding * general index * on the column by default, but the index name is the column name, not the constraint name of the foreign key.*

• 删除外键后，必须手动删除对应的索引
  
  [En]

  after deleting the foreign key, the corresponding index must be deleted manually*

添加外键约束

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 约束名;

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

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差不多）

Original: https://www.cnblogs.com/wht-de-bk/p/16006222.html
Author: T,a,o
Title: MySQL约束