20-外键约束 （多个表）

CREATE TABLE emp ( -- 创建emp表
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_name VARCHAR(30),  -- 部门名称
dep_location VARCHAR(30) -- 部门地址
);
-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳'); 

SELECT*FROM emp;  -- 发现数据有冗余   研发部广州 销售部深圳 重复太多
-- 做一个表的拆分 一个表存放部分一个存放地址



-- 解决方案：分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT -- 外键对应主表的主键
);
-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
SELECT * FROM employee;     -- 查询员工信息
SELECT *FROM department;    -- 查询部门信息  分两个地方查询

这样就是查询两张表 先员工表

找到dep-id 再去查部门信息表 

但是有弊端  如果这里在department表里删除了研发部门 呢就会出现问题 员工没有部门了 

所以外键约束 就是把dep-id 和id产生关联  发现两张表有引用关系  所以用了外键约束之后就不能随意删除了

* 外键约束：foreign key,让表于表产生关系，从而保证数据的正确性。
        1. 在创建表时，可以添加外键
            * 语法：
                create table 表名(
                    ....字段列表
                    外键列
                    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
                );

-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT, -- 字段列
NAME VARCHAR(20),-- 字段列
age INT,-- 字段列 
dep_id INT,-- 外键列  外键约束 就是把dep-id 和id产生关联 
CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id)
-- constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
);
-- 添加 2 个部门
INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳');
SELECT * FROM department;
-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

因为关联所以不能添加超出范围的外键

 因为外键关联不能删除

        2. 删除外键
            ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

 

        3. 创建表之后，添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
        
        
        4. 级联操作
            1. 添加级联操作
                语法：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称  FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;

部门id就能改了 而且自动同步到另一张表
            2. 分类：
                1. 级联更新：ON UPDATE CASCADE  

级联更新的作用是 department里的外键id改了  员工的外键也该了
                2. 级联删除：ON DELETE CASCADE 

级联删除是部门一删   员工信息直接也给删了