510约束_外键约束和511和约束_外键约束的级联操作

外键约束
外键约束:foreign key ,让表与表产生关系，从而保证数据的正确性
1.在创建表时，可以添加外键
语法：
CREATE TABLE 表名(
....(值)
外键列
CONSTRAINT 外键名称，FOREIGN KEY （外键列名称）REFERENCES 主表名称（主表列名称）
);

CREATE TABLE 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
('马云',55,'研发部','北京'),
('汤姆',19,'研发部','北京'),
('杰瑞',18,'研发部','北京'),
('马华腾',55,'财务部','青青草原'),
('红太狼',18,'财务部','青青草原'),
('灰太狼',19,'财务部','青青草原');
SELECT * FROM emp;

-- 数据有冗余出现相同数据

-- 表的拆分
/*
解决方案：
把他们两个部分分为两个表
创建部门表（id，dep_name,dep_location）
一方，主表
*/
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
DEP_NAME VARCHAR(30),
DEP_LOCATION VARCHAR(30)
);
-- 创建员工表（id，name，age，dep_id） 多方，从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
AGE INT,
DEP_ID INT,
CONSTRAINT epl_dept_fk FOREIGN KEY(DEP_ID)REFERENCES department(id)-- 新增外键
);
-- 添加两个部门
INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'财务部','青青草原');
-- 添加员工，deo_id表示员工所在部门
INSERT into employee (NAME,AGE,DEP_ID)VALUES
('马云',55,1),
('汤姆',19,1),
('杰瑞',18,1),
('马华腾',55,2),
('红太狼',18,2),
('灰太狼',19,2);
SELECT * FROM department;
SELECT * FROM employee;

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY epl_dept_fk;

-- 创建完表之后新增外键

ALTER TABLE employee ADD CONSTRAINT epl_dept_fk FOREIGN KEY(DEP_ID)REFERENCES department(id);

级联操作

根据以上已经创建好的表我们可以更改（NAME,AGE，DEP_NAME，DEP_LOCATION）唯独两个表的id不能进行更改原因很简单就是外键关联

如果执意需要更改的话我们需要先将employee表的DEP_ID 值为空再进行更改

-- 外键值可以为null但是不可以是不存在的null值
UPDATE employee SET DEP_ID = NULL WHERE DEP_ID=1; 
id值更改为5


-- 修改键值
UPDATE employee SET DEP_ID = 5 WHERE DEP_ID IS NULL; 

到employee 更改DEP_ID

 

还有一种方法就是

id更改后DEP_ID也就跟着自动更改

这样需要我们先删除外键，添加外键的时候我们需要对外键设置级联更新

 

-- 创建增外键并设置级联更新
ALTER TABLE employee ADD CONSTRAINT epl_dept_fk FOREIGN KEY(DEP_ID)REFERENCES department(id) ON UPDATE CASCADE;

-----

 

 

-- 创建增外键并设置级联更新也可以设置级联删除
ALTER TABLE employee ADD CONSTRAINT epl_dept_fk FOREIGN KEY(DEP_ID)REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;
-- 也可以直接设置级联删除
ALTER TABLE employee ADD CONSTRAINT epl_dept_fk FOREIGN KEY(DEP_ID)REFERENCES department(id) ON DELETE CASCADE;
设置级联删除，删除
将一号部门研发部删掉之后employee 表所有为1号部门的自动删除