08 常见约束

常见约束

含义：为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。约束是表级的强制规定

添加约束的时机

1.创建表时
2.修改表时（在没有数据的时候）

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

约束添加的分类

列级约束：位置在字段的后面，从语法上讲六大约束都支持，但外键约束没有效果
表级约束：位置在所有字段的后面，除了非空、默认，其他的都支持

主键和唯一对比

			保证唯一性  是否允许为空    	一个表中可以有多少个 	是否允许组合
主键		√			×				至多有1个           	√，但不推荐
唯一		√			√				可以有多个          	√，但不推荐

组合：将两个键组合为一个主键，那么只要这两个键不全相同就可以插入到表中。

创建表时添加约束

添加列级约束

语法：直接在字段名和类型后面追加约束类型即可，我们可以在一个列上添加多个约束。只支持默认约束、非空约束、主键约束、唯一约束

CREATE DATABASE students;
USE students;
DROP TABLE if exists stuinfo;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,#主键
	stuName VARCHAR(20) NOT NULL,#非空
	gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查(虽然没效果)或者用IN
	seat INT UNIQUE,#唯一
	age INT DEFAULT  18,#默认约束
	majorId INT REFERENCES major(id)#外键，虽然不报错，但是没效果
);

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

查看stuinfo中的所有索引，包括主键、外键、唯一

SHOW INDEX FROM stuinfo;

添加表级约束

语法：

1. 在各个字段的最下面【constraint 约束的名】 约束类型(字段名)
2. 非空约束和默认约束不支持
3. 主键的名字默认就是PRIMARY，修改也没有效果
4. 不起别名就会使用默认的名字（字段名）
5. 约束的名字不可以重复
6. 外键的名字最好是fk_本表的字段名_主表的字段名

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT,
	# 最好空一格
	CONSTRAINT pk（随便起的） PRIMARY KEY(id),#主键
	CONSTRAINT uq UNIQUE(seat),#唯一键
	CONSTRAINT ck CHECK(gender ='男' OR gender  = '女'),#检查（没效果）
	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键，必须存在major表
);

SHOW INDEX FROM stuinfo;

通用的写法：★

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)

);

列级约束和表级约束的比较

mysql本身不支持检查约束，所以以下讨论不包含检查约束

			位置			支持的约束类型				是否可以起别名
列级约束		对应列的后面		语法都支持，但外键没有效果	不可以
表级约束		所有列的下面		默认和非空不支持，其他支持	可以（对于主键起别名没有效果）

修改表时添加约束

1、添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束;

2、添加表级约束

alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
	id INT,
	stuname VARCHAR(20),
	gender CHAR(1),
	seat INT,
	age INT,
	majorid INT
)
DESC stuinfo;

1.添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20)  NOT NULL;

2.添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

3.添加主键
①列级约束

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

②表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

4.添加唯一

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

②表级约束

ALTER TABLE stuinfo ADD [CONSTRAINT uk_seat] UNIQUE(seat);

5.添加外键（列级没效果，所以只有表级）

ALTER TABLE stuinfo ADD 【CONSTRAINT fk_stuinfo_major】 FOREIGN KEY(majorid) REFERENCES major(id); 

修改表时删除约束

1.删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);# 默认是可以为空

2.删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

3.删除主键

alter table stuinfo modify column id int;
ALTER TABLE stuinfo DROP PRIMARY KEY;(建议)

4.删除唯一

SHOW INDEX FROM stuinfo;查到唯一键的名字
ALTER TABLE stuinfo DROP INDEX seat;

5.删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

### 分类
#### NOT NULL
非空约束，用于保证该字段的值不能为空，非空约束只能出现在表对象的列上，比如姓名、学号等。
null类型特征：所有的类型的值都可以是null；”空字符串”不等于null，0也不等于null。
#### DEFAULT
默认约束，用于保证该字段有默认值，比如性别，就是为了偷懒
#### PRIMARY KEY
•	主键约束相当于**唯一约束+非空约束**的组合，主键约束列不允许重复，也不允许出现空值
•	**如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复**
•	每个表最多只允许一个主键，**建立主键约束可以在列级别创建，也可以在表级别上创建**
•	MySQL的主键名总是PRIMARY，当创建主键约束时，系统默认会在所在的列和列组合上**建立对应的唯一索引**

**创建主键约束**

```sql
组合模式
CREATE TABLE emp6(
	id INT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(20),
	pwd VARCHAR(15),
	CONSTRAINT emp5_pk PRIMARY KEY(NAME, pwd)
);

UNIQUE

• 唯一约束，用于保证该字段的值具有唯一性，可以为空（允许出现多个空值）
• 同一个表可以有多个唯一约束或者多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。
• MySQL会给唯一约束的列上默认创建一个唯一索引

CHECK

检查约束【mysql中不支持】，就是语法不报错，但是没有效果，比如年龄、性别，限制到一个区间中

FOREIGN KEY

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

• 要求在从表设置外键关系
• 从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
• 主表的关联列必须是一个key（一般是主键或唯一）
• 插入数据时，先插入主表，再插入从表
• 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据，创建的时候反之
• 从表的外键值必须在主表中能找到或者为空
• 外键会默认生成索引
• 同一个表可以有多个外键约束

标识列又称为自增长列

含义：可以不用手动的插入值，系统提供默认的序列值，从1开始

特点：
1、标识列必须和主键搭配吗？不一定，但要求是一个key
2、一个表可以有几个标识列？至多一个！
3、标识列的类型只能是数值型，一般是int
4、标识列可以通过 SET auto_increment_increment=3;设置步长，可以通过手动插入值，设置起始值

一、创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;
CREATE TABLE tab_identity(
	id INT  primary key auto_increment,
	NAME varchar(20);
);

起始值不允许我们修改，即修改了也没有效果，修改环境变量，我们有一个取巧的方法可以做到，那就是插入第一个值的时候给定初值。

设置步长

SHOW VARIABLES LIKE '%auto_increment%';
SET auto_increment_increment=3;

修改表时设置标识列

类似于修改约束。

alter table tab_identity modify column id int primary key auto_increment;

修改表时删除标识列

alter table tab_identity modify column id int;