外键约束、级联操作及其应用场景

作用：

创建、更新、删除表时，保证各个关系表之间应用的一致性和合法性

更新或者删除时触发级联操作

1. 有一个部门表dept他有一个主键id
2. 有一个员工表，他有一个外键emp_no
3. 员工表中的外键引用了部门表中的主键作为他的外键
4. CONSTRAINT fk_dept_emp FOREIGN KEY(员工表中的外键列) REFERENCES 员工表(部门表中的主键)
5. 部门表是主表，员工表是从表
6. 级联场景：当创建一个学生时，如果没有对应的部门id就不能创建
7. 删除某个部门的时候，员工表中所有该部门的员工都被删除
8. 更新某部门的id时，员工表中的部门id都要改变

使用场景

• 使用方式
  • 现在主表中添加存放数据，再在从表中添加数据【先在部门表中添加数据，如何再在员工表中添加员工数据为员工设置部门id】
  • 如何员工表中的部门id设为null，则可以null，如果不是null则必须使用部门表中的部门id设为员工表中的员工id
• 主表更新，从表跟着更新
• 主表删除从表跟着删除
• 如果从表中使用了数据，则先删除从表中的数据在删除主表，才能顺利删除
• 班级表中的一班id变了，则学生表中的class_id也变了；删除班级表中的一班信息时，如果学生表中存在一班的学生，则不能删除班级表中的一班，除非把学生表中一班的学生全部赶出一班或让他们全部退学

代码实现

主表设计

CREATE TABLE departments(
dept_id INT PRIMARY KEY,
    dept_name VARCHAR(20)
);

从表设计

CREATE TABLE emp(
emp_id INT PRIMARY KEY,
    emp_name VARCHAR(20),
    dept_id INT,
    CONSTRAINT fk_dept_emp FOREIGN KEY(dept_id) REFERENCES departments(dept_id)
);

主表从表解释

• 在这两个表中departments作为主表

• emp作为从表

• 主表的主键dept_id作为从表的外键

• 从表中dept_id作为从表的外键

• 从表的dept_id是外键，外键引用主表departments中的主键作为从表的外键

插入测试

INSERT INTO emp (emp_id, emp_name, dept_id) VALUES (1, 'John', 1);

当执行上述代码的时候，只有当departments中的主键中有dept_id等于1这个值的时候插入才能成功

级联删除

当引用表(主表)中的行被删除时，从关联表（从表）中的相应行也会被自动删除

CREATE TABLE dept(
dept_id INT PRIMARY KEY,
    dept_name VARCHAR(10)
);

CREATE TABLE emp(
emp_id INT PRIMARY KEY,
    emp_name VARCHAR(20),
    dept_id INT,
    CONSTRAINT FORMARY KEY(dept_id) REFERENCES KEY dept(dept_id) ON DELETES CASCADE
);

级联更新

当引用表中的行的主键值发生更改时，关联表中的外键值也会自动更新为新的值

CREATE TABLE dept(
dept_id INT PRIMARY KEY,
    dept_name VARCHAR(10)
);

CREATE TABLE emp(
emp_id INT PRIMARY KEY,
    emp_name VARCHAR(20),
    dept_id INT,
    CONSTRAINT FORMARY KEY(dept_id) REFERENCES KEY dept(dept_id) ON UPDATE CASCADE
);

设为null

CREATE TABLE departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

CREATE TABLE employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50),
  dept_id INT,
  CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES departments (dept_id) ON DELETE SET NULL ON UPDATE SET NULL
);