mysql外键约束主表和从表确认 mysql建表外键约束

转载

编程小天才 2023-06-07 22:17:47

文章标签 mysql外键约束主表和从表确认 mysql 数据库 sql 外键约束 文章分类 MySQL 数据库

什么是外键约束？
外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。

1.外键约束

## 创建班级表 主表
create table t_class(
	cno int(4) PRIMARY KEY auto_increment, ## 主键自增
	cname VARCHAR(10) not null, ## 非空
	room char(4)
);
## 添加数据
INSERT 
into t_class 
VALUES(null,'java01班','r183'),
	  (null,'java02班','r154'),
	  (null,'大数据01班','r101');

-- 查看班级表
SELECT * FROM t_class;

## 创建学生表
CREATE TABLE t_student2(
  sno int(6) primary key auto_increment, 
  sname varchar(5) not null, 
  classno int(4)  
  -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
);

-- 添加学生数据
insert into t_student2 values (null,'张三',1),(null,'李四',1),(null,'王五',2);
insert into t_student2 values (null,'丽丽',3);

-- 查看学生表数据
SELECT * FROM t_student2;

-- 出现问题
-- 1.添加一个学生对应班级编号为4
INSERT INTO t_class values (null,'程程',4)
-- 2.删除班级二
DELETE FROM t_class where cno=2;
-- 出现问题的原因
-- 现在的外键约束没有用语法添加进去，知识逻辑上认为班级编号是外键，没有从语法上定义

-- 解决办法
-- 添加外键约束
-- 外键约束只有表级约束，没有列级约束
CREATE TABLE t_student2(
  sno int(6) primary key auto_increment, 
  sname varchar(5) not null, 
  classno int(4),
   -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
  CONSTRAINT fk_classmo FOREIGN KEY (classno) REFERENCES t_class (cno)
);

-- 在创建表以后添加外键约束：
ALTER TABLE t_student2
ADD CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class(sno);

2.外键策略

-- 删除学生表 从表
DROP TABLE t_student2;
-- 删除主表 主表

DROP TABLE t_class;
create table t_class(
	cno int(4) PRIMARY KEY auto_increment,
	cname VARCHAR(10) not null,
	room char(4)
);

INSERT 
into t_class 
VALUES(null,'java01班','r183'),
			(null,'java02班','r154'),
			(null,'大数据01班','r101');
-- 查看班级表
SELECT * FROM t_class;

CREATE TABLE t_student2(
  sno int(6) primary key auto_increment, 
  sname varchar(5) not null, 
  classno int(4),
	CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno)
);

insert into t_student2 values (null,'张三',1),(null,'李四',1),(null,'王五',2);
insert into t_student2 values (null,'丽丽',3);
-- 查看学生表
select * FROM t_student2;

-- 删除班级2：直接删除不行
-- 加入外键策略

-- 策略1：no action 不允许操作 
-- 通过操作sql完成：
-- 把班级2的学生对应的班级该为null
update t_student2
set classno=NULL
WHERE classno=2;
-- 然后再删除班级2
DELETE FROM t_class WHERE cno=2;


-- 策略二：cascade级联操作：操作主表的时候影响从表的信息
-- 先删除之前的外键约束
ALTER TABLE t_student2 DROP FOREIGN KEY fk_stu_classno;
-- 重新添加外键约束
alter TABLE t_student2 ADD CONSTRAINT fk_stu_
classn FOREIGN KEY t_class(cno) on UPDATE CASCADE on DELETE CASCADE;
-- 试试更新
update t_class set cno = 5 WHERE cno=3;
-- 试试删除
delete FROM t_class WHERE cno=5;


-- 策略三  set null 置空操作
-- 先删除之前的外键约束：
ALTER TABLE t_student2 DROP FOREIGN KEY fk_stu_classno;
-- 重新添加外键约束
alter TABLE t_student2 ADD CONSTRAINT fk_stu_classn FOREIGN KEY t_class(cno) on UPDATE set null on DELETE set null;
-- 试试genx
update t_class set cno=8 WHERE cno=1;