03_MySQL约束篇

1. 概述

1.1 概念/作用:

约束是数据库中一种用来限制表中数据的机制，是为了确保表中数据的正确性,有效性和完整性

1.2 分类:

四大约束:

• 主键约束,primary key
• 非空约束,not null
• 唯一约束,unique
• 外键约束,foreign key

其他约束:

• 默认约束,default

• 检查约束,check

  注：mysql不支持检查约束(check)，但是写上检查约束不会报错,但会忽略它的作用,只能使用 ENUM 类型 或者 触发器 实现检查约束,或者在应用程序中对数据进行检查再插入

1.3 添加约束:

一般来说添加约束是在创建表或者修改表的时候，在表中有数据后就不再添加约束了。

约束按照添加的位置可以分为两类：

• 列级约束：六大约束语法上都支持，但外键约束即使添加了也不会生效。

• 表级约束，除了非空约束、默认约束，其他的约束都支持。

• 创建列级约束的基本语法如下：

CREATE TABLE 表名(
    字段名 字段类型 列级约束,
    字段名 字段类型 列级约束,
    表级约束
);

• 创建表级约束的基本语法如下：

CREATE TABLE 表名(
    字段名 字段类型,
    字段名 字段类型,
    ...
    [CONSTRAINT 约束名] 约束类型(字段名);
    [CONSTRAINT 约束名] 约束类型(字段名);
    [CONSTRAINT 约束名] 约束类型(字段名);
    ....
);
-- 注：
-- constraint的英文含义有“约束”、“限制”的含义。
-- [CONSTRAINT 约束名]是一个可选项，可以写也可以不写
-- 约束类型就是上面提到的PRIMARY、UNIQUE、DEFAULT、CHECK等
-- 括号内的字段名是当前表中的字段

示例:

--　示例：创建一张学生表并添加表级约束
CRATE TABLE students(
	id int,
	name varchar,
	gender char(1),
	seat int,
	age in,# 默认约束，直接将默认值添加在后面即可
	major_id,
	-- 添加表级约束
	CONSTRAINT pk PRIMARY KEY(id),# 主键约束
	CONSTRAINT ck CHECK(gender='男' OR gender='女'),# 检查约束，但不会生效，其中CHECK()括号中可以写where后面支持的筛选条件
	CONSTRAINT uq UNIQUE(seat),# 唯一约束
	CONSTRAINT fk_students_major FOREIGN KEY(major_id) REFERENCES major(id)# #外键约束，其中CONSTRAINT是关键字，fk_students_major是外键名，可以任意书写，但一般规范是fk+从表名+主表名，以便于阅读，FOREIGN KEY()是外键约束名，major_id是从表中的字段，REFERENCES是关键字，major()是主表名称加括号，括号内的id是主表major中的主键id
);

但一般来说，通用写法是列级约束和表级约束一起使用，如下所示：

CREATE TABLE IF NOT EXISTS stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20),
	sex CHAR(1),
	age INT DEFAULT 18,
	seat INT UNIQUE,
	majorid INT,
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

• 创建完表添加约束基本语法如下:

-- 添加列级约束
ALTER TABLE 表名 MOBIFY COLUMN 字段名 字段类型 新约束;

-- 添加表级约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型（字段名） [外键的引用REFERENCES 主表(key名)];

1.4查询约束:

可以通过下面这个命令来查询表中所有的索引，包括主键、外键、唯一等。

SHOW INDEX FROM 表名;

1.5 删除约束:

非空约束,默认约束使用modify或者change替换原属性

主键约束,唯一约束和外键约束使用DROP删除

---

2. 主键约束

1. 主键约束:非空且唯一,是表中数据的唯一标识,并且在mysql中会默认为主键使用B+TREE树创建主键索引,索引名为PRIMARY
2. 正常情况下,一张表就只能有一个字段是主键

2.1 添加主键约束:

• 在创建表时添加:

  在创建表时在字段末加上primary key,示例:

create table name(
    id int primary key -- 添加主键约束	
);
    
CREATE TABLE name(
    id INT,
    PRIMARY KEY(id)
);
    
-- 复合约束:
CREATE TABLE name(
    id INT,
    name VARCHAR(30),
    CONSTRAINT PK_ID_NAME PRIMARY KEY(id,name)
);

• 在创建表之后添加:

ALTER TABLE name 
modify 
id int primary key;
-- change需要修改字段名

ALTER TABLE name
ADD PRIMARY KEY(id); 
ALTER TABLE name 
ADD CONSTRAINT
PK_ID PRIMARY KEY(id);
-- 与使用ALTER命令修改索引的命令一模一样
-- 证明mysql创建主键约束同时会给主键创建主键索引

​

2.2 删除主键约束:

-- 正确示范:
ALTER TABLE name
DROP 
PRIMARY KEY; -- 因为主键就只有一个,所以不需要写明具体删除哪个字段
-- 错误示范:
ALTER TABLE name
modify 
id int;
-- 不能通过modify替换原有属性的方法替换原来的id而删除主键,主键是表约束而不是列约束
-- 所以删除主键约束要使用drop

2.3 主键的自增长

• 自增长概念:如果某一列是数值型,可以使用auto_increment完成值的自增长

  自增长列又叫标识列,可以不用给标识列手动插入值,系统会提供默认增长值,即可以给主键赋值为null,自增长会自动给主键编号

  自增长列必须是一个key(主键或唯一约束)

  一个表最多只能有一个自增长列,所以一般和主键搭配使用

  标识列的数据类型必须是数值型

添加自增长:

• 在创建表时添加主键约束并完成自增长(在primary key后面加auto_increment):

create table name(
	id int primary key auto_increment 
    -- 添加主键约束并完成自增长
)

• 创建表之后添加自增长:

ALTER TABLE name
modify
id int auto_increment

• 修改步长:

SET auto_increment_increment = 3;

• 删除自增长:

ALTER TABLE name 
modify 
id int;-- 自增长是针对字段的

删除主键约束前，如果有自增长需要先删除自增长,如果不删除自增长就无法删除主键约束!

---

3. 非空约束

3.1 添加非空约束

• 创建表时添加(在字段声明后面加上not null):

create table name(
	id int not null -- 添加非空约束	
);

• 创建完表之后添加:

ALTER TABLE name
modify 
id int not null; 
-- 除主键约束外其他约束在表中不一定唯一,需要加上具体字段名

3.2 删除非空约束;

ALTER TABLE name
modify 
id int;
-- 非空约束不是针对表的,所以可以通过modify或者change替换原属性值的方法删除非空约束,不能通过DROP删除
-- 使用change的方法:
ALTER TABLE name
modify 
id id int;-- 使用change添加和删除需要更改字段名,因为change用于对字段比较大的更改操作

---

4. 唯一约束

• 注意点:创建唯一约束的字段可以有null值,但是只能有一个

  创建唯一约束后,mysql会自动为这个字段使用B+TREE添加唯一索引,索引名为字段名

4.1 添加唯一约束:

• 创建表时添加(在字段声明末尾加上unique):

create table name(
	id int unique -- 添加唯一约束	
);
-- 复合约束:
CREATE TABLE t_user(
    id INT,
    name VARCHAR(30),
    CONSTRAINT UN_ID_NAME UNIQUE(id,name)
);

• 创建完表之后添加:

ALTER TABLE name 
modify 
id int unique; 

ALTER TABLE name 
ADD UNIQUE(id);
ALTER TABLE name 
ADD UNIQUE KEY(id);

ALTER TABLE name 
ADD CONSTRAINT 
UN_ID UNIQUE(id);
ALTER TABLE name 
ADD CONSTRAINT
UN_ID UNIQUE KEY(id);

-- 题外话:SQLServer创建唯一索引(SQLServer没有modify和change)
ALTER TABLE 表名
ADD
CONSTRAINT 约束名 UNIQUE (列名);
-- 通过ADD CONSTRAINT(添加约束)的方式添加

4.2 删除唯一约束:

ALTER TABLE name DROP INDEX id;

---

5. 外键约束

1. 外键约束对应的字段只能是主键或者唯一约束修饰的字段

2. 插入数据时，先插入主表中的数据，再插入从表中的数据。

   删除数据时，先删除从表中的数据，再删除主表中的数据。

5.1 添加外键约束:

• 创建从表时添加:

create table name(
	...,
	外键列,
	[constraint 外键名称
	foreign key(外键列名称)
	references 主表名称(主表列名称)
)

• 创建完表之后添加外键:

ALTER TABLE name 
ADD constraint 外键名称
foreign key(外键列名称)
references 主表名称(主表列名称);

5.2 删除外键约束

ALTER TABLE 从表名
DROP FOREIGN KEY 外键名;

5.3 级联操作:

1. 添加级联操作(可以在创建表时添加,也可以在创建完表之后添加,不一一示范,都是在添加外键末尾加入级联操作):

ALTER TABLE name 
ADD constraint 外键名称
foreign key(外键列名称)
references 主表名称(主表列名称)
ON UPDATE CASCADE
ON DELETE CASCADE;

2. 分类:
   • 级联删除:ON DELETE CASCADE,删除主表中的数据时，从表中的数据随之删除
   • 级联更新:ON UPDATE CASCADE 更新主表中的数据时，从表中的数据随之更新
   • 删除置空:ON DELETE SET NULL删除主表中的数据时，从表中的数据置为空

---

6. 默认约束

6.1 添加:

-- 创建表时:
CREATE TABLE name(
	id INT DEFAULT  3
);

-- 创建表后:
ALTER TABLE name
MODIFY id INT
DEFAULT  2;

ALTER TABLE name
CHANGE id id INT
DEFAULT  2;

6.2 删除:

ALTER TABLE name
MODIFY id INT;

ALTER TABLE name
CHANGE id id INT;