数据库学习2024/3/18

十三章_约束

1.约束的基础知识

1.1为什么要约束？为了保护数据的完整性

1.2 什么叫约束？对表中的字段进行限制

1.3 约束的分类：

角度一：约束的字段个数

单列约束 vs 多列约束

角度二：约束作用范围：

列级约束：将此约束声明在对应字段的后面

表级约束：在表中所有字段都声明完，在所有字段的后面声明的约束。

角度三：约束的功能

① not null(非空约束)

② unique(唯一性约束)

③ primary key(主键约束)

④ foregin key(外键约束)

⑤ check(检查约束)

⑥ default(默认值约束)

1.4 如何添加约束

create table 时添加约束

ALTER TABLE 时增加 / 删除约束

2.如何查看约束

information_schema数据库名（系统库）

table_constraints表名称（专门存储各个表的约束）

SELECT * FROM information_schema.table_constraints

WHERE table_name = '表名称';

3.not null(非空约束)

3.1 create table 时添加约束

案例：

CREATE DATABASE test13

USE test13

CREATE TABLE te

(

id INT NOT NULL,

salary DECIMAL

);

3.2ALTER TABLE 时增加约束

ALTER TABLE 表名

MODIFY 字段数据类型 NOT NULL;

3.3ALTER TABLE 时增删除约束

ALTER TABLE 表名

MODIFY 字段数据类型 NULL;

4.unique(唯一性约束)

4.1 create table 时添加约束

CREATE TABLE te

(

id INT UNIQUE, #列级约束

email VARCHAR(20),

salary DECIMAL,

#表级约束

#constraint uk_表名_作用字段 unique(作用字段)

CONSTRAINT uk_te_email UNIQUE(email) / UNIQUE(email) );

结论：在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。

结论：可以在声明为unique的字段上添加NULL，而且可以多次添加NULL

4.2 ALTER TABLE 时增加约束

方式1：

ALTER TABLE 表名

MODIFY 字段数据类型 UNIQUE;

方式2：

ALTER TABLE 表名

ADD UNIQUE KEY(字段名称);

4.3 复合唯一性约束

案例

CREATE TABLE te

(

id INT UNIQUE, #列级约束

email VARCHAR(20),

salary DECIMAL,

#表级约束

#constraint uk_表名_作用字段 unique(作用字段)

CONSTRAINT uk_te_email_sal UNIQUE(email,salary) / UNIQUE(email,salary) );

4.4 ALTER TABLE 时删除约束

添加唯一性约束的列上也会自动创建唯一索引。

删除唯一约束只能通过删除唯一索引的方式删除。

删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。

如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同；如果是组合列，那么默认和() 中排在第一个的列名相同。也可以自定义唯一性约束名。

如何删除唯一索引

ALTER TABLE 表名

DROP INDEX 唯一索引名;

5 primary key(主键约束):非空且唯一

一个表中最多只能有一个主键约束

表名唯一：PRIMARY

CREATE TABLE te

(

id INT PRIMARY KEY, #列级约束

email VARCHAR(20),

salary DECIMAL,

#表级约束

#constraint uk_表名_作用字段 unique(作用字段)

PRIMARY KEY(email,salary) #没有必要取名字 );

5.2 ALTER TABLE 时增加约束

ALTER TABLE 表名

ADD PRIMARY KEY(字段名称)

5.3 ALTER TABLE 时增加约束(在实际开发中不会删除主键约束)

ALTER TABLE 表名

DROP PRIMARY KEY;

6. auto_increment（自增长列）:某个字段的值自增

特点：1.一个表只能有一个auto_increment

2.只能添加在主键或唯一

3.只适用于整数

在开发时，一旦我们给主键添加了auto_increment,则添加字段时不需要添加主键字段的值

6.2ALTER TABLE 时增加auto_increment

ALTER TABLE 表名

MODIFY 字段名字段类型 AUTO_INCREMENT;

6.3ALTER TABLE 时删除auto_increment

ALTER TABLE 表名

DROP 字段名字段类型;

6.4 MySQL 8.0新特性—自增变量的持久化

特点:MySQL 8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变，

都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。

7. foregin key(外键约束):限定某个表的某个字段的引用完整性。

7.1 create table 时添加约束

有主表和从表（父表和子表），先创建父表再创建子表

父表

CREATE TABLE dept

(

dept_id INT PRIMARY KEY,

dept_name VARCHAR(15)

)

子表 CREATE TABLE emp

(

emp_id INT,

emp_name VARCHAR(15),

emp_department_id INT,

#表级约束

CONSTRAINT fk_emp_dept_id FOREIGN KEY (emp_department_id) REFERENCES dept(dept_id)/

FOREIGN KEY (emp_department_id) REFERENCES dept(dept_id)(会随机生成索引名) );

7.2添加信息时，只有主表有的信息从表才可以添加

7.3 ALTER TABLE 时增加 FOREIGN KEY

ALTER TABLE 表名

ADD #表级约束

CONSTRAINT fk_emp_dept_id FOREIGN KEY (emp_department_id) REFERENCES dept(dept_id)

7.4 on update cascade on delete set null的用法：

父表和子表同步修改 父表删除数据，子表变为NULL

7.5 ALTER TABLE 时删除 FOREIGN KEY

(1)第一步先查看约束名和删除外键约束

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名

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

（2）第二步查看索引名和删除索引。（注意，只能手动删除）

SHOW INDEX FROM 表名称; #查看某个表的索引名

ALTER TABLE 从表名 DROP INDEX 外键约束名;

8.check(检查约束)：检查某个字段的值是否符号xx要求，一般指的是值的范围

8.1create table 时添加约束

MySQL5.7不支持，MySQL8.0支持

CREATE TABLE emp

(

emp_id INT,

emp_name VARCHAR(15),

emp_salary DECIMAL(10,2) CHECK(salary > 2000) #check(条件)

);

9.default(默认值约束)

9.1 CREATE TABLE添加约束