第三课 常见约束

• 含义：一种限制，用于限制表中的数据，保证表中的数据的准确性和一致性
• 分类
  • NOT NULL：非空约束，用于保证该字段的值不能为空（姓名性别等）
  • DEFAULT：默认约束，用于保证该字段有默认值
  • PRIMARY KEY：主键约束，用于保证字段的值具有唯一性，并且非空
  • UNIQUE：唯一约束，用于保证该字段的值具有唯一性，可以为空
  • CHECK：检查约束（MySQL不支持），对传入的值进行判断
  • REFERENCES：外键约束（FOREIGN KEY），用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，从表添加外键约束，用于引用主表中某列的值
• 约束的添加分类：
  • 列级约束：六大约束语法上都支持，但外键约束没有效果
  • 表级约束：除了非空约束和默认约束，剩下的都支持

CREATE TABLE 表名（
	字段名 字段类型 列级约束，
	字段名 字段类型 列级约束
	表级约束
）

1 创建表时添加约束

1.1 添加列级约束

• 直接在字段名和类型后面追加约束类型即可

# 列级约束
CREATE TABLE stuInfo (
	# 主键约束
	id INT PRIMARY KEY,
	# 非空约束
	stuName VARCHAR (20) NOT NULL,
	# 检查约束
	gender CHAR (1) CHECK (gender = '男' OR gender = '女'),
	#唯一约束
	seat INT UNIQUE,
	#默认约束
	age INT DEFAULT 18,
	# 外键约束
	majorID INT REFERENCES major (id)
);

CREATE TABLE major (
	id INT PRIMARY KEY,
	majorName VARCHAR (20)
);

1.2 添加表级约束

• 语法

CREAT TABLE 表名(
	列名 数据类型,
	列名 数据类型,
	列名 数据类型,
	【CONSTRAINT】 约束名 约束方式(列名),
	【CONSTRAINT】 约束名 约束方式(列名),
	【CONSTRAINT】 约束名 约束方式(列名),
);

# 表级约束
CREATE TABLE stuInfo2 (
	id INT,
	stuName VARCHAR (20),
	gender CHAR (1),
	seat INT,
	age INT,
	majorID INT,
	# 主键约束
	CONSTRAINT pk PRIMARY KEY (id),
	# 检查约束
	CONSTRAINT ck CHECK (gender = '男' OR gender = '女'),
	#唯一约束
	CONSTRAINT uq UNIQUE (seat),
	# 外键约束
	CONSTRAINT fk_stuInfo2_major FOREIGN KEY (majorID) REFERENCES major (id)
);

1.3 通用写法

• 通常外键约束的时候使用表级约束，其他约束直接在列名后追加即可

CREATE TABLE stuInfo (
	# 主键约束
	id INT PRIMARY KEY,
	# 非空约束
	stuName VARCHAR (20) NOT NULL,
	# 检查约束
	gender CHAR (1) CHECK (gender = '男' OR gender = '女'),
	#唯一约束
	seat INT UNIQUE,
	#默认约束
	age INT DEFAULT 18,
	majorID INT,
	# 外键约束
	CONSTRAINT fk_stuInfo2_major FOREIGN KEY (majorID) REFERENCES major (id)
)

1.4 主键和唯一的对比

	主键	唯一
保证唯一性	√	√
是否允许为空	×	√
是否允许单表多个	×	√
是否允许组合出现	√，但不推荐	√，但不推荐

注：唯一约束全列只允许一个数据为空

# 组合主键，组合唯一
CREATE TABLE stuInfo3 (
	id INT,
	stuName VARCHAR (20),
	gender CHAR (1),
	seat INT,
	age INT,
	majorID INT,
	# 主键约束
	CONSTRAINT pk_id_stuName PRIMARY KEY (id,stuName),
	#唯一约束
	CONSTRAINT uq_seat_age UNIQUE (seat,age),
);

1.5 外键

• 要求在从表设置外键关系

• 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求

• 主表的关联列必须是一个KEY(一般是主键或唯一键)

• 插入数据时，先插入主表，再插入从表（学生选修课程，此时，学生信息是主表，选修课程表是从表）

• 删除数据时，先删除从表，再删除主表

• 一个字段可以有多种约束，各种约束之间使用空格进行连接即可

• 当要删除从表中的数据时，由于和主表发生外键联系，因此无法直接从从表中将数据删除，因此存在以下两种级联操作

# 级联删除（将数据从从表中删除时，主表中的一整条数据也将被删除）
ALTER TABLE 表名 ADD CONSISTENT 约束名 FOREIGN KEY(被约束列名) REFERENCES 外联表(外联表列名) ON DELETE CASCADE;
# 级联置空（将数据从从表中删除时，主表中的外键连接字段的值被置为空，但是不会删除主表中的数据）
ALTER TABLE 表名 ADD CONSISTENT 约束名 FOREIGN KEY(被约束列名) REFERENCES 外联表(外联表列名) ON DELETE SET NULL;

2 修改表时添加（删除）约束

2.1 添加约束

• 基本语法：

# 添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 列名 类型 新约束;
# 添加表级约束
ALTER TABLE 表名 ADD 【CONSTRAINT 约束名】 约束类型(列名);

• 添加非空约束
  ALTER TABLE 表名 MODIFY COLUMN 列名 类型 NOT NULL;
• 添加默认约束
  ALTER TABLE 表名 MODIFY COLUMN 列名 类型 DEFAULT 默认值;
• 添加主键约束
  ALTER TABLE 表名 MODIFY COLUMN 列名 类型 NOT NULL; # 列级约束
ALTER TABLE 表名 ADD PRIMARY KEY(列名); # 表级约束
• 添加唯一约束
  ALTER TABLE 表名 MODIFY COLUMN 列名 类型 UNIQUE; # 列级约束
ALTER TABLE 表名 ADD UNIQUE(列名); # 表级约束
• 添加默认约束
  ALTER TABLE 主表名 ADD FOREIGN KEY(主表列名) REFERENCES 从表名（从表列名-->在从表中该列存在某种约束条件）;

2.2 删除约束

• 删除非空约束
  ALTER TABLE 表名 MODIFY COLUMN 列名 列类型 NULL;
ALTER TABLE 表名 MODIFY COLUMN 列名 列类型;
• 删除默认约束
  ALTER TABLE 表名 MODIFY COLUMN 列名 列类型;
• 删除主键约束
  ALTER TABLE 表名 DROP PRIMARY KEY;
• 删除唯一约束
  ALTER TABLE 表名 DROP INDEX 约束名;
• 删除外键约束
  ALTER TABLE 表名 DROP FOREIGN KEY 约束名;

3 标识列

• 含义：可以不用手动的插入值，系统提供默认的序列值，也称为自增长列
• 特点
  • 标识列必须是某个被约束的列
  • 一个表中只能有一个标识列
  • 标识列必须是数值型（INT FLOAT DOUBLE）
  • 标识列可以设置系统默认步长，但是不支持修改系统默认的起始值
  • 可以通过手动插入第一条数据来改变起始值，但不更改系统默认的起始值常量，系统默认起始值常量始终为1

3.1 创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;

# 创建标识列
CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20)
);

TRUNCATE TABLE tab_identity;

# 插入数据
INSERT INTO tab_identity VALUES(NULL,'A'),(NULL,'B');
INSERT INTO tab_identity (name) VALUES('C'),('D');

# 显示系统默认常量
SHOW VARIABLES LIKE '%auto_increment%';
/*
	auto_increment_increment = 1 # 标识列每次递增的步长
	auto_increment_offset = 1 # 起始值
*/

# 更改系统常量步长（系统常量起始值不支持更改）
SET auto_increment_increment = 3;

# 更改初始值（非系统常量）
INSERT INTO tab_identity VALUES (100,'E'); # 先行规定第一条的起始值后追加其他数据
INSERT INTO tab_identity (name) VALUES('F'),('G');

3.2 修改表时设置（删除）标识列

# 修改表时设置标识列
ALTER TABLE 表名 MODIFY COLUMN 列名 类型 约束条件 AUTO_INCREMENT;
# 修改表时删除标识列
ALTER TABLE 表名 MODIFY COLUMN 列名 类型;