有一个东西一直在我脑海中是个很烦的东西, 但是这东西不搞清楚会阻碍自己的前进. 自己做项目 demo 永远只能用一张表...
所以今天还是学习了下外键希望能够搞明白一些...
百度上搜索外键的作用 "保持数据的完整性, 一致性","参照完整性"... 什么的, 我想信很多小伙伴都不明白到底什么意思.
首先我么要搞明白, 为什么要使用外键,
比如有两张表(在最下面我会附上表的创建 SQL 语句, 以及外键的添加和关联删除时候的语句)
表一 (student1) 有字段: stu_id(学号),stu_name(姓名),stu_gender(性别),stu_nation(民族),stu_idcard(身份证号)
表二(score1) 有字段: stu_id(学号),stu_chinese(语文成绩),stu_math,stu_english,stu_pe(体育成绩)
通过关联 stu_id, 就可以把表一表二在操作的时候可以拼凑成一张大表,
字段::stu_id(学号),stu_name(姓名),stu_gender(性别),stu_nation(民族),stu_idcard(身份证号),stu_chinese(语文成绩),stu_math,stu_english,stu_pe(体育成绩)
不用外键的话, 貌似也可以正常操作, 比如, 你可以任意添加给任何一张表任何信息, 但是如果有了外键(student1 的 stu_id 引用 score1 中的 stu_id)
你就不能先给 student1 执行 insert 操作, 因为 student1 中的 stu_id 和 score1 中的 stu_id 有关联
所以就必须得先给 score1 表赋值 INSERT INTO score1 VALUES('201890001',95,88,97,95);
随后才能够 INSERT INTO student1 VALUES('201890001','张三','男','汉','123468744123597014'); 因为在 insert 给 student1 表的时候, stu_id 必须与之前赋值给 score1 表中的一致, 也就是说
给从表赋值的时候, 关联的字段值必须在主表中能找到! 否则就添加失败, 这就是外键的作用!
比如: 如果我给 score1 中添加了学号为 1 的一名学生的学生成绩, 当我给 student1 表中添加了一个学号为 2 的学生信息的时候, 是添加不成功的, 因为没有关联上, 除非 score1 中有学号为 2 的学生成绩信息. 只有这样, 才能够有参照作用, 所以才保证了参照完整性.
补充: 什么是从表, 什么是主表, student1 中有语句 CONSTRAINT fk_stu_id_score1 FOREIGN KEY(stu_id) REFERENCES score1(stu_id) 就意味着, score1 是主表, student1 是从表(虽然我也经常头晕... 但想着, 主表的关联字段必须是主键, 它就大一点, 它就是主表把~...)
刚刚提到了, 主表的关联字段必须是主键. 是这么个意思: student1 中的 stu_id 和 score1 中的 stu_id 关联, score 是主表, stu_id 必须是 score1 表的主键, 而 stu_id 在 student1 中是不是主键无所谓.
接下来就是主外键关联删除的删除:
score 中的信息删除时候, 从表 student1 中的关联信息怎么解决, 比如主表中学号为 1 的学生成绩信息被删除了, 那么从表 student1 中的学号为 1 的学生信息怎么办, 有三种解决方式:
1, 禁止删除(不允许删除, 会报错)
2, 将参照要删除数据的子表对应数据置空.(从表中学号为 1 的学生信息为空, null)在创建外键的后面加上 ON DELETE SET NULL(后面会附上练习表的创建语句)
3, 将参照要删除数据的子表对应数据删除.(从表中学号为 1 的学生信息删除)在创建外键的后面加上 ON DELETE CASCADE
注意如果是置空或者删除的话, 要注意能不能删除或者置空, 比如你设置了那个字段是 NOT NULL, 主表删除的时候, 从表中的也会删除, 但是从表中你又定义了 NOT NULL, 所以就会报错. 这也是发 现的一个报错的原因...
(
其实我写到这才发现我练习的主外键关联对应的表有逻辑问题, 应该再 score1 上添加外键, 让 student1 当主表, score1 当从表, 所以当 student1 中学号为 1 的学生信息删除了, score1 从表中学号为 1 的数据怎么办? 当然是删除了, 你 学生都没了(比如退学), 还要保存他的成绩信息干什么? 当然这都是题外话, 我也懒得从头再改了, 相信大家应该看得懂. 逻辑翻转想一下就会明白,
或者说, 有一个表示会员表, 一个表是订单表,
会员表要引用订单表中的信息, 他们通过会员 ID 字段关联, 当会员表中的某一个会员信息删除了, 根据正常需要, 基本都是把订单表中对应的信息也删除了, 因为你会员都没了要保存订单信息干什么? 所以就是上述的第三种解决方式,
)
好了, 回归到之前的假设场景, student1 是从表, score1 是主表, 将错就错下去吧...
我觉得明白了以上的概念逻辑后, 其他的能稍微想通一些了吧, 比如写 sql 语句报错的时候就能明白为什么要先这样, 才能那样... 接下来我附上我练习实验用的表的 sql 语句.
- USE test;
- /* 要先删除从表, 才能删除主表(从表中需要引用主表中的字段, 先删除主表, 那么从表就不成立因为从表中还有着和主表的关联关系)*/
- DROP TABLE IF EXISTS student1;
- DROP TABLE IF EXISTS score1;
- CREATE TABLE score1(
- stu_id VARCHAR(50) NOT NULL,
- stu_chinese DOUBLE,
- stu_math DOUBLE,
- stu_english DOUBLE,
- stu_pe DOUBLE,
- CONSTRAINT pk_stu_id_score1 PRIMARY KEY(stu_id)
- );
- CREATE TABLE student1(
- stu_id VARCHAR(50),
- stu_name VARCHAR(50),
- stu_gender VARCHAR(5),
- stu_nation VARCHAR(50),
- stu_idcard VARCHAR(50) NOT NULL,
- CONSTRAINT pk_stu_idcard_student1 PRIMARY KEY(stu_idcard),
- CONSTRAINT fk_stu_id_score1 FOREIGN KEY(stu_id) REFERENCES score1(stu_id) ON DELETE CASCADE
- );
- /* 下面两条插入语句顺序颠倒会报错 */
- INSERT INTO score1 VALUES('201890001',95,88,97,95);
- INSERT INTO student1 VALUES('201890001','张三','男','汉','123468744123597014');
这是我学习时候参考的两个网页地址
- https://www.cnblogs.com/bhlsheji/p/5332910.html
- https://blog.csdn.net/bisal/article/details/20898055
欢迎大家讨论下啊, 我也是初学希望多多指教, 有哪里不对的请指出讨论一下~ 谢谢...
来源: https://www.cnblogs.com/deepSleeping/p/9525779.html