MySQL中“约束（constraint）”操作语法结构及应用场景

MySQL中“约束（constraint）”操作语法结构及应用场景：

1.概念

针对表中的数据，进行额外的限制，就称为约束。

2.

约束的分类：
2.1 角度一：从功能上分：
not null 非空约束
unique 唯一性约束
primary key 主键约束
foreign key 外键约束
check 检查约束
default 默认值约束

2.2 角度二：约束的作用范围、声明位置
列级约束 VS 表级约束

2.3 角度三：从约束的作用的列的个数：
单列约束 VS 多列约束

---------------------------------------（分割线）------------------------------------

3.

如何使用约束：
情况一： 在create table 时，与创建表的字段同时执行。
情况二： 在alter table 时，对约束进行操作：添加约束、删除约束。

---------------------------------------（分割线）------------------------------------

4.

not null : 非空约束
说明： not null 约束只能只用列级约束来定义
我们新建一个emp2表作为研究对象，代码如下：

CREATE TABLE emp2(
id INT NOT NULL,  # 列级约束（一次性约束一列）
emp_name VARCHAR(15) NOT NULL,  # 列级约束
salary DOUBLE(10,2),
email VARCHAR(26)
);

备注：以上代码中，我们通过 NOT NULL 对 id 和 emp_name 行进了非空约束。此时，表 emp2 的id列和emp_name 列被声明了非空约束，在添加数据的时候，这两列不能为空。

我们测试一下这里的非空约束：
1.在有约束的情况下，给表格添加数据的正确操作

INSERT INTO emp2(id,emp_name,salary,email)
VALUES(1,'Tom',6000,'Tom@163.com');

我们通过查询语句来查询一下此时的表格数据，查询结果如下：

通过查询结果说明以上的添加操作成功。

++++++++++++++++（分割线）+++++++++++++++++

在有约束的情况下，添加的错误操作类型1（有添加了非空约束的字段被给了空值）（在添加数据的时候定义了被非空约束的相关字段）

INSERT INTO emp2(id,emp_name,salary,email)
VALUES(NULL,'Tom',6000,'Tom@163.com');

++++++++++++++++（分割线）+++++++++++++++++

在有约束的情况下，添加的错误操作类型2（有添加了非空约束的字段被给了空值）（在添加数据的时候没有定义被非空约束的相关字段）

INSERT INTO emp2(id,salary,email)
VALUES(1,6000,'Tom@163.com');

&&&&&&&&&&&&（分割线）&&&&&&&&&&&&&

接下来，我们介绍一下，如何删除非空约束(其实是一种对约束的修改)
举例：将emp2 表中字段id由非空约束 not null 改为可以为空，参考代码如下：

ALTER TABLE emp2
MODIFY id INT NULL;

此时，我们在添加数据的时候，字段 id 对应的数据是可以为空的，例如：

INSERT INTO emp2(id,emp_name,salary,email)
VALUES(NULL,'Tom',6000,'Tom@163.com');

数据顺利添加成功，此时，我们通过查询语句来查询一下此时的表格数据，查询结果如下：

我们看到，此时，表id字段对应的数据出现了一个null，添加成功。

&&&&&&&&&&&&（分割线）&&&&&&&&&&&&&

接下来，我们介绍一下，如何添加非空约束(其实是一种对约束的再次修改)
**注意：**这种在表格已经有一些数据的情况下，添加非空约束的时候一定要注意这个非空的约束是否和之前的数据有矛盾的地方。
举例1:

ALTER TABLE emp2
MODIFY id INT NOT NULL;

在这里由于之前添加了一个id为null的数据了，所以此时再添加非空约束会报错。

++++++++++++++++（分割线）+++++++++++++++++

像这里对email做非空约束的添加的时候就是可以通过的，因为之前数据里面没有出现email为null的情况。

ALTER TABLE emp2
MODIFY email VARCHAR(25) NOT NULL;

**特别强调：**在运用非空约束的时候，尽量在造表的时候就把非空约束加进去，避免后期调整约束带来数据和约束的矛盾。

---------------------------------------（分割线）------------------------------------

5.

unique 约束 （唯一性约束）
方法一：创建表的同时，添加约束。参考代码如下：

 CREATE TABLE emp3(
id INT  UNIQUE,
emp_name VARCHAR(15),
salary DOUBLE(10,2),
email VARCHAR(25),
# 以下是表级约束的写法举例(这里我们只选中了一个email做约束，实际应用中可以选择更多)
CONSTRAINT emp3_email_uni UNIQUE(email)
);

以上代码中，我们给字段id和字段email 添加了unique唯一性约束。

先给表emp3添加一行数据以供测试：

INSERT INTO emp3(id,emp_name,salary,email)
VALUES(1,'Tom',6000,'Tom@163.com');

此时，我们通过查询语句来查询一下此时的表格数据，查询结果如下：

接下来，我们在此基础上添加数据，来测试被声明唯一性约束的字段的数据唯一性。

错误的写法1（由于id的重复）：

INSERT INTO emp3(id,emp_name,salary,email)
VALUES(1,'Tom',6000,'Juiny@163.com');

&&&&&&&&&&&&（分割线）&&&&&&&&&&&&&

错误的写法2（由于email的重复）：

INSERT INTO emp3(id,emp_name,salary,email)
VALUES(2,'Tom',6000,'Tom@163.com');

&&&&&&&&&&&&（分割线）&&&&&&&&&&&&&

可以通过的写法1（唯一性约束unique管不了null）：

INSERT INTO emp3(id,emp_name,salary,email)
VALUES(3,'Tom',6000,NULL);

此时，我们通过查询语句来查询一下此时的表格数据，查询结果如下：

&&&&&&&&&&&&（分割线）&&&&&&&&&&&&&

可以通过的写法2（声明为unique的字段的多行数据是可以被多次赋值为null的）
说明：字段列中出现多个null，是不被认为重复的。（唯一性约束unique对null无效）。

INSERT INTO emp3(id,emp_name,salary,email)
VALUES(4,'Tom',6600,NULL);

此时，我们通过查询语句来查询一下此时的表格数据，查询结果如下：

++++++++++++++++（分割线）+++++++++++++++++

接下来，我们讨论一下如何删除唯一性约束（unique）
声明为唯一性约束的字段上，会自动添加索引
删除唯一约束只能通过删除唯一索引的方式删除。
删除时需要指定唯一索引名，唯一索引名就和唯一约束名一样。
如果创建唯一约束时未指定名称，如果是单列，就默认和列名相同，如果是组合列，那么默认和( )中排在第一个的列名相同。也可以自定义唯一性约束名。

例如，我们要删除表emp3中的字段id的唯一性约束，代码如下：

ALTER TABLE emp3
DROP INDEX id;

++++++++++++++++（分割线）+++++++++++++++++

给表中某个字段添加唯一性约束
例如，需要给表emp3中的字段id添加唯一性约束（给添加的唯一性约束命名为emp3_id_nui），代码如下：

ALTER TABLE emp3
ADD CONSTRAINT emp3_id_nui UNIQUE(id);

---------------------------------------（分割线）------------------------------------

6.

主键约束 ： primary key 约束
主键约束的作用是列满足：非空且唯一
优点：通过主键约束作用的列，查询数据的话，可以唯一定位表中的一条记录（提高查询准确率）
限制：一个表中只允许最多定义一个主键约束

我们先新建一个表emp4，作为研究对象

CREATE TABLE emp4(
id INT ,# id int primary key   # 这是列级约束 
emp_name VARCHAR(15),
salary DOUBLE(10,2),
email VARCHAR(25),
# 表级约束
CONSTRAINT emp4_id_pk PRIMARY KEY(id)
);

在以上的代码中，我们涉及到了两种声明主键约束的方法,一种是列级约束id int primary key ，一种是表级约束CONSTRAINT emp4_id_pk PRIMARY KEY(id)。
此时，我们查看一下新建表格的各字段性质，结果如下：

我们看到，字段id已经被声明了主键约束。

先在表格中添加一个数据以供测试，参考代码如下：

INSERT INTO emp4(id,emp_name,salary,email)
VALUES(1,'Tom',6000,'Tom@126.com');

此时，我们通过查询语句查询一下表emp4的数据，结果如下：

同样是给主键约束的字段添加数据，我们来看看错误的写法，希望大家引以为戒。
错误的写法1(由于id和之前数据的重复，违背了主键约束的唯一性)

INSERT INTO emp4(id,emp_name,salary,email)
VALUES(1,'Tom',6000,'Tom@126.com');

错误的写法2（由于id为null，违背了主键约束的非空性）

INSERT INTO emp4(id,emp_name,salary,email)
VALUES(NULL,'Tom',6000,'Tom@126.com');

++++++++++++++++（分割线）+++++++++++++++++

开发中常用的声明主键约束的方式（主键约束一般会伴随着AUTO_INCREMENT自增长出现），参考代码如下：

CREATE TABLE emp5(
id INT PRIMARY KEY AUTO_INCREMENT, # 这里列级约束，AUTO_INCREMENT为自增长，在未给值的时候实现自动递增
emp_name VARCHAR(15),
salary DOUBLE(10,2),
email VARCHAR(25)
);

备注: AUTO_INCREMENT为自增长，在未给值的时候实现自动递增。

接下来，我们试试这种伴随着自增长的主键约束的效果
我们添加一组数据，不给id赋值：

INSERT INTO emp5(emp_name,salary,email)
VALUES('Tom',6000,'Tom@126.com');

将此添加命令运行三次，我们通过查询语句来查询一下此时的表格数据，查询结果如下：

说明：
这里，我们在自定义添加数据的时候，未给id赋值，
但由于建表的时候，给id声明了主键约束且定义了AUTO_INCREMENT自增长，所以在添加数据的时候，id会自动递增。

++++++++++++++++（分割线）+++++++++++++++++

删除表emp4主键约束的操作:

ALTER TABLE emp4
DROP PRIMARY KEY;

删除主键约束，不需要指定主键名(被主键的字段)，因为一个表只有一个主键。删除主键约束后，非空约束还依然存在。

++++++++++++++++（分割线）+++++++++++++++++

给表emp4添加主键约束:
类型一：只对一个字段添加主键约束

ALTER TABLE emp4
ADD CONSTRAINT emp4_id_pk PRIMARY KEY(id);# 只对一个字段做主键约束

类型二：在一个表里同时对多个字段进行主键约束（主键约束只能设置一个，但可以同时作用于多个字段）

ALTER TABLE emp4
ADD PRIMARY KEY(id,email); # 这里就是同时对id和email两个字段进行主键约束。 

++++++++++++++++（分割线）+++++++++++++++++

修改主键约束（可以理解成另一种添加主键约束的方法）

ALTER TABLE emp4
MODIFY id INT PRIMARY KEY;

---------------------------------------（分割线）------------------------------------

7.

外键约束
外键约束涉及到两个表格，一个主表，一个从表。这里我们创建两个表格作为研究对象。
创建主表：

CREATE TABLE dept06(
dept_id INT,
dept_name VARCHAR(10)
);

创建从表(外键约束一般是声明在从表里的)

CREATE TABLE emp06(
id INT,
emp_name VARCHAR(15),
dept_id INT,
# 表级约束
CONSTRAINT emp06_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept06(dept_id)
);

备注：CONSTRAINT emp06_dept_id_fk FOREIGN KEY(dept_id) REFERENCES dept06(dept_id) 是在通过表级约束声明一个外键约束。

注意：外键约束作用在主表上的列，必须声明有主键约束或唯一性约束。
此时，我们给主表中被外键关联的列添加主键约束：

ALTER TABLE dept06
ADD CONSTRAINT dept06_dept_id_pk PRIMARY KEY(dept_id);

通过外键约束之后，若相应的主表中的列没有数据的话，在从表中添加数据是不成功的。
例如：
此时添加数据会报错：

INSERT INTO emp06(id,emp_name,dept_id)
VALUES(1,'Tom',10);

正确的做法是先在主表相应的列中添加数据

INSERT INTO dept06(dept_id,dept_name)
VALUES(1,'IT');

此时，再次向从表添加数据就可以通过了

INSERT INTO emp06(id,emp_name,dept_id)
VALUES(1,'Tom',10);

++++++++++++++++（分割线）+++++++++++++++++

注意：
主表的数据不能随意删除，有被外键约束的列，删除数据的时候会影响到从表里数据和主表通过外键对应的关系。
数据也不能随意修改，要保证修改以后，通过外键约束联系起来的列可以对应起来。

例如，这里就不能随意修改dept06表里数据，否则会导致emp06表里有一些员工没有对应部门：

UPDATE dept06
SET dept_id = 20
WHERE dept_id = 10;

这个案例中，这里如果把表dept06里的部门号10都改成了20，会导致原本10号部门的人没有部门了。

补充：
级联操作（相当于捆绑）
ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
ON DELETE SET NULL(级联置空): 当父表中的列被删除时，子表中相应的列的值置空（归为null）

---------------------------------------（分割线）------------------------------------

8-检查约束 ：check
check在mysql中无效
check在Oracle中是可以执行的
下面，我们演示一下在Oracle中如何使用check检查约束
新建一个表格emp7，并在其中声明check检查约束，代码如下：
例如，这里我们要设置一个salary>3000的检查约束

CREATE TABLE emp7(
id INT,
emp_name VARCHAR(15),
salary DOUBLE(10,2) CHECK(salary > 3000)
);

此时，在Oracle中创建以上表emp7的话，要求添加的数据中，salary必须满足 > 3000

---------------------------------------（分割线）------------------------------------

9-默认值约束 ： default
新建一个表格emp8，并在其中声明default默认约束，代码如下：

CREATE TABLE emp8(
id INT,
emp_name VARCHAR(15),
salary DOUBLE(10,2) DEFAULT 4000
);

此时，我们在表emp8里添加数据的时候,就算不给salary赋值，salary也会默认为4000
例如：

INSERT emp8(id,emp_name)
VALUES(1,'Jeck');

此时，我们通过查询语句来查询一下此时的表格数据，查询结果如下：

我们看到，salary已经被默认赋值成了4000

这种情况下，如果强制给salary赋其他值，就会报错。