【八，MySQL】基础篇--约束（外键约束、以及删除/更新行为）

一，约束概述

二，操作演示
根据需求，完成表的创建

很奇怪为什么在age字段后添加comment就会报错无法创建，应该是版本问题不支持检查约束

create table employee(
    id int primary key auto_increment comment 'ID，主键',
    name varchar(10) not null unique comment '姓名',
    age int check (age>0 && age<=120),
    status char(1) default '1' comment '状态，默认为1',
    gender char(1) comment '性别'
)comment '员工表2' charset=utf8;

insert into employee(name, age, status, gender) values ('张三',18,'1','男');
insert into employee(name, age, status, gender) values ('李四',130,'1','男');
insert into employee(name, age, status, gender) values ('王五',20,'1','男');
insert into employee(name, age, status, gender) values ('赵六',21,'1','男');
insert into employee(name, age, status, gender) values ('张无忌',22,'1','男');
insert into employee(name, age, status, gender) values ('王语嫣',23,'0','女');
insert into employee(name, age, status, gender) values ('张三丰',24,'1','男');
insert into employee(name, age, status, gender) values ('鸠摩智',30,'1','男');
insert into employee(name, age, status, gender) values ('乔峰',31,'1','男');
#即使受到约束导致插入失败，也会申请到一个自增的id
insert into employee(name, age, status, gender) values ('慕容复',32,'1','男');
insert into employee(name, age, status, gender) values (null,32,'1','男');
insert into employee(name, age, status, gender) values ('周芷若',33,'0','女');
insert into employee(name, age, gender) values ('虚竹',33,'男');
select * from employee;

drop table employee;

三，外键约束

# =====================================================外键约束=====================================================
create table dept(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null comment '部门名称'
)comment '部门表' charset =utf8;

insert into dept(id,name) values (1,'研发部'),(2,'财务部'),(3,'市场部'),(4,'销售部'),(5,'经办部');
insert into dept(id,name) values (2,'财务部');

create table emp(
    id int auto_increment comment 'ID主键' primary key ,
    name varchar(50) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导id',
    dept_id int comment '部门id'
)comment '员工表' character set = utf8;

drop table emp;

insert into emp(id, name, age, job, salary, entrydate, dept_id) values
(1,'张三丰',50,'总裁',500000,'2000-01-01',1);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(2,'张三',20,'助理',5000,'2020-01-01',1,1);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(3,'李四',25,'经理',15000,'2015-02-17',1,1);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(4,'王五',30,'经理',20000,'2014-01-01',1,2);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(5,'赵六',20,'助理',5000,'2021-01-01',4,2);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(6,'田七',23,'助理',5000,'2020-01-01',1,3);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(7,'张无忌',20,'员工',4000,'2020-01-01',6,3);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(8,'井中月',20,'组长',7000,'2019-01-01',1,4);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(9,'周芷若',20,'助理',5000,'2021-01-01',8,4);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(10,'王语嫣',20,'程序员鼓励师',5000,'2022-01-01',1,1);

select * from dept;
select * from emp;

# 添加外键
#如果出现报错：[23000][1452] Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-cbc_20`, CONSTRAINT `fk_emp_dept_id` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`))
#是因为子表（emp）中关联的字段里的数据是在父表（dept）中没有的，建立外键约束时必须保证子表中的数据包含在父表内
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

# 删除外键
alter table emp drop foreign key fk_emp_dept_id;

四，删除、更新行为

# 级联操作-删除/更新行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null;

图形化界面操作直接经典白学。