【MySQL】数据库约束

目录

0. 约束类型概览

1. not null 非空约束

2. unique 唯一约束

3. primary key 主键约束

3.1 单列主键

3.2 自增约束

3.3 复合主键

3.4 主键冲突时的强制更新

4. foreign key 外键约束

5. default 默认约束

6. check 检查约束

---

0. 约束类型概览

数据库约束是指对数据库表中的数据所施加的规则或条件，⽤于确保数据的准确性和可靠性。这些约束可以是基于数据类型、值范围、唯⼀性、⾮空等规则，以确保数据的正确性和相容性。

约束类型	说明
NOTNULL⾮空约束	指定⾮空约束的列不能存储NULL值
DEFALUT默认约束	当没有给列赋值时使⽤的默认值
UNIQUE唯⼀约束	指定唯⼀约束的列每⾏数据必须有唯⼀的值
PRIMARYKEY主键约束	NOTNULL和UNIQUE的结合，可以指定⼀个列或多个列，有助于防⽌数据 重复和提⾼数据的查询性能
FOREIGNKEY外键约束	外键约束是⼀种关系约束，⽤于定义两个表之间的关联关系，可以确保数据 的完整性和⼀致性
CHECK约束	⽤于限制列或数据在数据库表中的值，确保数据的准确性和可靠性

1. not null 非空约束

特性：

• 插入数据时，约束列不能是 NULL 值。
• 一张表可以有多个非空约束列。

-- 例1：创建一个学生表，学生名为NULL时，这条记录是不完整的

drop table if exists student;
 create table student(  
   id bigint,
   name varchar(20)
 );  
 desc student;

 # 【目前没有任何约束】

 # 插入含null值的数据

insert into student values (1, null);

SELECT * FROM student; 

可以看到，在没有not null非空约束的情况下，列是可以插入null值的。

---

接下来在有非空约束的情况下，再次尝试插入相同的数据会发生什么：

-- 例2：此时约束学⽣名的列不能为NULL

drop table if exists student;
create table student (    #【给name列加上非空约束】
 id bigint,
 name varchar(20) NOT NULL
);
 desc student;

从表结构可以看到，name列不再可以取Null值。

# 尝试插入相同的数据：

# 插入的数据中，name列只要是不为Null都能正常插入：

insert into student values (1, '张三');
SELECT * FROM student;

2. unique 唯一约束

特性：

• 约束列插入数据时，不能重复插入表中已存在的数据。
• Null值不被约束。（Null值可重复插入）
• 一张表可以多个唯一约束列。

-- 例1：尝试插入重复数据、尝试插入null

drop table if exists student;
create table student (    
 id bigint UNIQUE,                # id列被唯一约束
 name varchar(20) NOT NULL
);
DESC student;

# 插入测试数据

insert into student values (1, '张三');

SELECT * FROM student;

# 插入id列重复的数据：（1，‘李四’）

# 可以重复插入null：（null，‘李四’）（null，‘王五’）

insert into student values (NULL, '李四');
insert into student values (NULL, '王五');
SELECT * FROM student;

3. primary key 主键约束

特性：

• 插入特性上，主键约束相当于非空约束与唯一约束的结合。即约束列在插入时不可重复且不能有Null值。
• 主键可以由多个列组成。
• 一张表只能有一个主键。

3.1 单列主键

-- 1.重构学生表，为ID列添加非空和唯一约束（单列主键）

drop table if exists student;
create table student(
  id bigint PRIMARY KEY,    # 定为主键的列通常使用的是bigint类型
  name varchar(20) NOT NULL
);
desc student;

insert into student values (1, '张三');

# 尝试在主键列插入null值

# id列出现重复元素，拒绝插入

---

-- 例2：一张表不能有多个主键

create table student1(
  id bigint PRIMARY KEY auto_increment, 
  sno varchar(20) PRIMARY KEY,   
  name varchar(20) NOT NULL
);
# 这里出现了两个主键，会报错

3.2 自增约束

使用 auto_increment 关键字可以使用自增约束：自动为每条新记录生成唯一的、递增的标识符。

约束的要求：

1. 一张表只能有一个约束列。
2. 自增约束只能用于整型类型的列，通常使用BIGINT类型。
3. 自增约束下，约束列至少要具有Unique性质。所以能使用自增约束的列只有：单列主键、复合主键的其中一列、唯一列。

合法的使用：

• 自增约束列在插入数据时，可以不指定该列的数值（或者使用Null值来占位），因为自增值会在上一行基础上自动加一。
• 如果要指定约束列的插入值时，那么插入的值最好大于或等于当前自增值。可以小于当前自增值，但必须是没有重复出现，而且自增值不会变小。
• 指定插入约束列的不能为负值。

-- 例1：在id主键列加上自增约束

drop table if exists student;
create table student(
  id bigint PRIMARY KEY auto_increment,    # 定为主键的列通常使用的是bigint类型
  name varchar(20) NOT NULL
);
desc student;

# 不指定自增列的插入，会自动填入自增值：

# 没有指定id列的值
insert into student (name) values('张三');
insert into student (name) values('李四');
SELECT * FROM student;

# 在主键上插入null，也是使用主键的自增值（可以使用null占位）

INSERT into student VALUES(NULL,'王五');
SELECT * FROM student;

# 用户自己明确指定主键的值，观察自增值的变化：

show create table student;

当前的自增值是4

INSERT into student VALUES(100,'赵六');
show create table student;    # 自增值从4变成了101

用户输入的是100，那么自增值(下次插入时自动填充的值)就会在上一行数据的基础上自动加一，100+1等于101。

---

-- 例2：约束列的值可能是离散的，用户可以插入未使用的数值，但自增值变大后不会回退

select * from student;

由于例2的操作，自增值变成101.

# 尝试插入3~101中未使用的值

INSERT into student VALUES(4,'赵四');
select * from student;

可以看到插入成功了，我们再看看自增值有没有变成5：

可以看到，自增值没有变小，依旧是101

3.3 复合主键

复合主键（***posite Primary Key）是指由两个或多个列共同组成的主键。

• 由于复合主键由两个以上的列组成，所以在定义时必须在列定义后单独定义。
• 复合主键的重复值判断：当复合主键中所有列的组合出现重复时，会拒绝数据的插入；如果只是部分列的组合出现重复，那么还是可以正常插入。

-- 例1：无自增约束的复合主键

drop table if exists student;
create table student(
  sno varchar(20),  
  name varchar(20),
  gender bool,
  primary key (sno, name)    # 单独定义主键
);
desc student;

# 插入测试数据

insert into student (sno, name, gender) VALUES ('1','张三',1);

SELECT * FROM student;

# sno列与name列都出现重复值时，才是复合主键重复

# 部分列重复是可以正常插入的：此处sno列重复了

insert into student (sno, name, gender) VALUES ('1','李四',1);
SELECT * FROM student;

-- 例2：含自增约束的复合主键

drop table if exists student;
create table student(
  id bigint auto_increment,        # id列使用了自增约束
  name varchar(20),
  primary key (id, name)    # 单独定义主键
);
desc student;

insert into student (id, name) VALUES ('1','张三');
SELECT * FROM student;

# 复合主键下，自增约束不会强制要求约束列的单一重复：

insert into student (id, name) VALUES ('1','李四');
SELECT * FROM student;

可以看到，虽然id列存在单列重复，但是还是可以正常插入

# 自增约束仅对约束列的自增值进行自增

insert into student (name) VALUES ('王五');
insert into student (name) VALUES ('赵六');
SELECT * FROM student;

3.4 主键冲突时的强制更新

当插入主键的数据已经存在于表中时，该插入会被拒绝，这就是主键冲突。

如果我们在插入前预料到可能会出现主键冲突问题，但是一定插入这条数据(例如之前的数据是错的，这次插入的数据是对的必须替换)，那么可以使用主键冲突的更新操作。

语法：

方式一：

insert into 表名 (插入列表) values 多行数据 on duplicate key update 修改的内容；

• “修改的内容” 内部的格式是 “要修改的列 = 值 | 表达式” (此处的等于号 = 表示赋值)。多个列的修改用逗号隔开。
• 如果主键冲突了，那么会触发UPDATE。
• 修改时，可以保留未指定字段的旧内容。
• on duplicate key update 可以理解为：当主键冲突时执行后面的修改操作。

方式二：

replace into 表名 (插入列表) values 多行数据；

• 这是MySQL的扩展语法，代码不具有可移植性。
• 如果主键冲突了，那么按顺序触发 DELETE 和 INSERT。
• 不保留未指定字段的旧内容(因为被delete掉了)。如果存在未指定的字段，那么该字段必须要有default默认约束。

不过，方式二的性能较低，方式一的性能较高。推荐选择方式一。

-- 例如：

drop table if exists student;
create table student(
  id bigint PRIMARY KEY auto_increment,  
  name varchar(20) NOT NULL,
  sno varchar(20) NOT NULL
);
INSERT INTO student(name, sno) VALUES('张三', 100001);
SELECT * FROM student;

# 方式一的演示：

INSERT INTO student(id, name, sno) VALUES(1, '张三', 100100)
on duplicate key update sno = 100100;  
SELECT * FROM student;

插入(1, '张三', 100100)会发生主键冲突， 把旧的sno从100001修改成100100

# 方式二的演示：

replace into student(id, name, sno) VALUES(1, '王五', 222222);
SELECT * FROM student;

主键id依旧是冲突，replace会把旧的数据行delete掉，再插入新的数据

4. foreign key 外键约束

外键⽤于定义主表和从表之间的关系。

语法：foreign key (从表关联列) references 主表名(主表关联列) 

特点：

• 主表关联的列必须是主键或唯⼀约束，而从表的关联列用外键约束foreign key单独标记出来。
• 从表关联列的数据类型要与主表关联列保持一致。
• 从表的列被foreign key约束后，其取值 必须包含在主表关联列的取值里面。

删除操作的注意事项：

• 从表 以及 从表的行数据，针对它们的删除操作无限制。
• 主表行数据的删除：主表中被引用的行不可被删除，未被引用的行可以删除。
• 整个主表的删除：
  • 需要先删除整个从表，才能删除主表。
  • 或者先取消关联关系，才能删除主表。
  • 再或者 暂时先取消数据库对外键约束的检查，才能删除主表，删除后要恢复对外键的检查。

-- 例如：学生表(从表)的班级编号 与 班级表(主表)的编号进行关联 

# 创建班级表(主表)，并初始化数据

drop table if exists class;
create table class (
 id bigint primary key auto_increment,
 name varchar(20) not null
 );
desc class;

insert into class (name) values ('java01'), ('java02'), ('java03'), ('C++01'), ('C++02');
SELECT * FROM class;

#重构学生表(从表)，加入外键约束
drop table if exists student;
create table student(
  id bigint PRIMARY KEY auto_increment,
  name varchar(20) NOT NULL,
  age int,
  class_id bigint,
  FOREIGN KEY (class_id) references class(id)
);
desc student;

insert into student(name, age, class_id) VALUES('张三', 18, 1);
SELECT * FROM student;

# 1【不能插入外键约束中没有的值】

insert into student(name, age, class_id) VALUES('李四', 18, 100);

class表中id列的取值不存在100

# 2【从表一旦用真实的外键数据与主表建立联系，那么主表中被引用的外键行就不能被删除】

insert into student(name, age, class_id) VALUES('李四', 18, 2);
insert into student(name, age, class_id) VALUES('王五', 18, 3);
insert into student(name, age, class_id) VALUES('赵六', 18, 3);
SELECT * FROM student;

此时从表已经引用了主表id为3的数据，所以不能删除主表中id为3的数据：

delete from class where id = 3;

delete from class where id = 4;
SELECT * FROM class;

# 3【主表中没被引用的行还是可以删除的】

id为4，name为C++01 的数据被删除了。

# 4【只要从表没有被销毁(即使没有引用)，那么主表就不能被销毁】 

 DELETE FROM student;
 SELECT * FROM student;

此时从表中已经没有数据了，但是表与表之间的关联关系还存在，所以主表还不能被删除：
 drop table class;

 # 4.1先drop从表后，可以drop主表
 drop table student;
 drop table class;

 # 4.2取消从表外键关系后也能drop主表
 alter table student drop FOREIGN KEY student_ibfk_1;
 drop table class;

 # 4.3 禁用外键检查,删除后再立即恢复
 SET FOREIGN_KEY_CHECKS = 0;
 DROP TABLE if exists class;
 SET FOREIGN_KEY_CHECKS = 1;

5. default 默认约束

DEFAULT 约束⽤于向列中插⼊默认值。

特性：插入数据时，如果约束列并没有设置值，那么它将会被赋予默认约束中的默认值。

# 1【此时age没有default约束，当插入数据没有age的值时，填入的是null】

drop table if exists student;
create table student (
 id bigint PRIMARY KEY auto_increment,
 name varchar(20) not null,
 age int
);
INSERT INTO student(name) VALUES('张三');
SELECT * FROM student;

# 2【age加上default约束后，当插入数据没有age的值时，填入的是默认值】

drop table if exists student;
create table student (
 id bigint PRIMARY KEY auto_increment,
 name varchar(20) not null,
 age int DEFAULT 18     # 默认值为18
);

desc student;

INSERT INTO student(name) VALUES('张三');
SELECT * FROM student;

6. check 检查约束

check 检查约束⽤于限制列中可接受的数据值。

格式：check (条件表达式)

特点：

• 一张表中，check约束可以应用于⼀个或多个列。
• 在8.0.16开始全⾯⽀持CHECK约束，之前的版本会忽略CHECK的定义。
• 指定约束列的值插入时，该值必须满足check约束定义时的条件表达式。
• check约束不会对Null值生效。

-- 示例：

drop table if exists student;
create table student(
 id bigint PRIMARY KEY auto_increment, 
 name varchar(20) not null,
 age int DEFAULT 18 check(age >= 16),        # 两种写法均可
 gender char(1),
 check (gender = '男' or gender = '女')
);

# 正常的插入

INSERT into student VALUES(null, '张三', 16, '男');
INSERT into student VALUES(null, '小丽', 30, '女');
SELECT * FROM student;

# 超出check约束的范围则拒绝执行

# 插入的年龄小于16

INSERT into student VALUES(null, '李四', 1, '男'); 

# 插入的性别既不是“男”也不是“女”

INSERT into student VALUES(null, '王军', 16, '无');

# check不对null值生效

INSERT into student VALUES(null, '李四', null, '男');
INSERT into student VALUES(null, '王军', 16, null);

SELECT * FROM student;

null值也是成功插入，不受到检查。

# 如果要同时两个列之间的约束，必须把check约束单独写出来（表级约束写法）

drop table if exists t_check;
create table t_check (
 c1 int check(c1 <> 0),
 c2 int check(c2 > 0),
 c3 int,
 check(c3 >= c2)    # 同时比较列c3和c2，需要把check约束单独写出来
);

---

本期分享完毕，感谢大家的支持Thanks♪(･ω･)ﾉ