MySQL中的约束问题

MySQL中的约束

1、概念：约束是作用于表中字段上的规则上的，用于限制存储在表中的数据

2、目的：保证数据库中的数据正确性、有效性、完整性

3、分类

约束汇总表

约束名称	描述	关键字
非空约束	限制字段的数据不能为空	NOT NULL
主键约束	MySQL数据库中的每张表都应该保证有一个主键，其作用就是保证字段的所有数据都是唯一的，不重复的	PRIMARY KEY
唯一约束	报数字段的所有数据都是唯一的，不重复的	UNIQUE
默认约束	保存数据的时候，如果没有指定字段的值，则此时就会使用默认事先设定的值	DEFAULT
检查约束	检查约束（8.0.16版本之后才出现）保证字段的值达到某个标准	CHECK
外键约束	可以理解为绑定多张表，建立练习，保证数据的一致性和完整性	FOREIGN KEY

注意:

        约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

4、实例：

学生表

字段名	字段含义	字段类型	约束条件	约束关键字
id	id唯一标识	int	主键，并且自动增长	PRIMARY KEY        AUTO_INCREMENT
name	姓名	varchar(20)	不为空，并且唯一	NOT NULL        UNIQUE
age	年龄	int	大于0.并且小于等于120	CHECK
status	状态	char(1)	如果没有指定该值，默认为1	DEFAULT
gender	性别	char(1)	无	无

建表语法：

CREATE TABLE student(

        id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',

        name VARCHAR(20) NOT NULL UNIQUE COMMENT '学生姓名',

        age TINYINT CHECK (age > 0 and age <=120) COMMENT '学生年龄',

        status CHAR(1) DEFAULT '1' COMMENT '学生的学籍状态：1：在读 0：休学',

        gender CHAR(1) COMMENT '学生性别'

);

5、检验我们的约束条件

第一、主键约束：

# 检查我们的主键约束
INSERT INTO student(id,name,age,status,gender) VALUES (1,'张三',23,1,'男'),(2,'李四',25,1,'男');
INSERT INTO student(name,age,status,gender) VALUES ('翠花',18,1,'女');

结果：可以看出，当我们未指定我们的主键值是，根据自增约束，我们的主键值会自动增加

第二、唯一约束以及默认约束：

# 唯一约束、非空约束
INSERT INTO student(age,gender) VALUES (19,'男');
INSERT INTO student(name,age,gender) VALUES ('翠花',19,'男');

代码不能正常执行通过 第三、检查约束，请在运行前检查安装MySQL的版本（8.0.16后）

# 检查约束
INSERT INTO student(name,age,gender) VALUES ('李星云',21,'男');
INSERT INTO student(name,age,gender) VALUES ('袁天罡',300,'男');

 第四、外键约束（用于多张表数据之间的数据一致性和完整性）

数据准备：

# 员工表和部门表
CREATE TABLE dept_test(
    id INT COMMENT '部门编号',
    name VARCHAR(20) UNIQUE COMMENT '部门名称'
);
INSERT INTO dept_test(id,name) VALUE (1,'研发部'),(2,'市场营销部'),(3,'测试部'),(4,'运维部'),(5,'人事部'),(6,'美工部');

CREATE TABLE emp_test(
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  name VARCHAR(20) COMMENT '员工姓名',
  age TINYINT COMMENT '员工年龄',
  job VARCHAR(10) COMMENT '员工岗位',
  dept_id INT COMMENT '员工所属部门'
);

INSERT INTO emp_test(id,name,age,job,dept_id) VALUES (1,'张三',21,'Java开发',1),
                                                     (2,'李四',25,'测试岗',3),
                                                     (3,'翠花',18,'测试岗',3),
                                                     (4,'麻子',20,'运维岗',4),
                                                     (5,'秦始皇',120,'嵌入式开发',1),
                                                     (6,'老子',18,'老总',5),
                                                     (7,'小妖怪',21,'PPT',6);

展示：

 试想，作为一家公司来说，人员的调动以及员工离职、部门的增添删除是很正常不过了。假象我们的公司由于不景气，美工部必须要进行部门删除，部门里边的人员信息也因该随着删除（一致性和完整性），但是如果我们没有使用外键约束，将会发生如下的情况

加入删除我们的美工部

 

理应美工部中的人员信息不再展示，但是

 从以上不妨能看出，如果不把两个表练习起来，我们的数据是不可能保持一致性的，所以就要使用到我们的外键约束了（foreign key）

该说不说：在MySQL中，使用外键约束并不要求两个表都必须有主键。但是，至少一个表必须有主键或者唯一键。

/*
    名词解释：
    constraint:约束，理解为增加约束
    references:关联，参照
    添加约束的语法：一个是建表时，一个是后期增删
    第一种、建表时：
    create table 表名(
        字段名 数据类型,
        ...
        [constraint][外键名] foreign key (外键字段名) reference 主表（主表列名）
    );

    第二种、建表后：
    增：alter table 表名 add constraint 外键名 foreign key (作为从表中的外键字段名) reference 主表(主表列名);
    删：alter table 表名 drop 外键名;

    前面的操作只能使得我们不能随意的删除表中的元素，并不能很好的管理我们的主表和从表的删除和更新行为，接下来的属性配置能让我们更好的配置我们的表
    行为                           解释
    NO ACTION(RESTRICT)           两种配置一样的用法，都是当我们的在父表中删除/更新所对应的记录时，首先会先检查该记录是否有对应的外键，如果
                                  有，则不允许删除/更新

    CASCADE                       当在父表中删除/更新对应的记录时，首先先检查是否有对应的外键，如果有，则也删除/更新外键所在的子表

    SET NULL                      当在父表中删除对应记录时，首先检查该记录是否有对应的外键，如果有则设置子表中该外键的值未NULL，这就要求外
                                  键的值为NULL（面试题：外键的值能为NULL吗？）
    SET DEFAULT                   父表有变更时，子表将外键设置为一个默认的值，但是Innodb数据库引擎不支持
    使用语法：
    alter table 表名 add constraint 外键名 foreign key (作为从表中的外键字段名) reference 主表(主表列名) on update cascade.....;

 */
# 给dept_test表中的id添加主键（报错：[HY000][1822] Failed to add the foreign key constraint. Missing index for constraint 'emp_dept_fk' in the referenced table 'dept_test'）
ALTER TABLE dept_test ADD PRIMARY KEY(id);

# 增加约束
alter table emp_test add constraint emp_dept_fk foreign key (dept_id) references dept_test(id);

# 删除约束
alter table emp_test drop foreign key emp_dept_fk;

 添加外键后不能随意删除

要使用删除/更新操作，看下方，展示一个即可：

# 增加删除/更新约束
alter table emp_test add constraint emp_dept_fk foreign key (dept_id) references dept_test(id) on UPDATE cascade on delete cascade ;

alter table emp_test add constraint emp_dept_fk foreign key (dept_id) references dept_test(id) on update set null on delete set null ;

# 总结：删除/更新命令必须有，改动的就是cascade和set null两个部分

 执行之后：

 原始：

对比：小妖怪已经不见了。

 使用：

alter table emp_test add constraint emp_dept_fk foreign key (dept_id) references dept_test(id) on update set null on delete set null ;

 把人事部也销了

结果：