美文网首页软件测试SQL极简教程 · MySQL · MyBatis · JPA 技术笔记 教程 总结工作生活
五、SQL–索引/约束⑥(外键约束)

五、SQL–索引/约束⑥(外键约束)

作者: 小小一颗卤蛋 | 来源:发表于2020-04-26 19:09 被阅读0次

引出：
当一些信息在表中重复出现时，就要考虑要将它们提取到新的表中，并在源表中引用新创建的中的数据。

如：很多作者都著有不止一本著作，所以在保存书籍信息的时候，应该把作者信息放到单独的表中，创建表的SQL语句如下：

MYSQL、MSSQLServer：
CREATE TABLE T_AUTHOR(
FId VARCHAR(20) PRIMARY KEY,
FName VARCHAR(100),
FAge INT,FEmail VARCHAR(20));

CREATE TABLE T_Book(
FName VARCHAR(100),
FPageCount INT,)
FAuthorId VARCHAR2(20)); 

Oracle：
CREATE TABLE T_AUTHOR(
FId VARCHAR2(20) PRIMARY KEY,
FName VARCHAR2(100),
FEmail VARCHAR2(20));

CREATE TABLE T_Book(
FName VARCHAR2(100),
FPageCount NUMBER (10),
FAuthorId VARCHAR2(20));

DB2：
CREATE TABLE T_AUTHOR(
FId VARCHAR(20) NOT NULL PRIMARY KEY,
FName VARCHAR(100),FAge INT,
FEmail VARCHAR(20));

CREATE TABLE T_Book(
FId VARCHAR(20) NOT NULL PRIMARY KEY,
FName VARCHAR(100),FPageCount INT,
FAuthorId VARCHAR(20));

插入数据：

INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail) VALUES("1","lily",20,"lily@cownew.com");
INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail) VALUES("2","kingchou",23,"kingchou@cownew.com");
INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail) VALUES("3","stef",28,"stef@cownew.com");
INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail) VALUES("4","long",26,"long@cownew.com");
INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail) VALUES("5","badboy",31,"badboy@cownew.com");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId) VALUES("1","About Java",300,"1");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId) VALUES("2","Inside Ruby",330,"2");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId) VALUES("3","Inside Curses",200,"5");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId) VALUES("4","Python InAction",450,"4");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId) VALUES("5","WPF Anywhere",250,"1");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId) VALUES("6","C# KickStart",280,"3");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId) VALUES("7","Compling",800,"1");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId) VALUES("8","Faster VB.Net",300,"5");

表T_Book 的FAuthorId字段存储的是代表每个作者的主键值，如果要查询一本书的作者信息，那么只要按照FAuthorId字段的值到T_AUTHOR 表中查询就可了。不过这样的表结构仍然存在问题：
1)不能约束表T_Book的FAuthorId字段中存储在表T_AUTHOR中不存在的值。
如：执行下面的SQL语句向T_Book表中插入新数据：

INSERT INTO T_Book(
FId,FName,FPageCount,FAuthorId)
VALUES("9","AboutWinCE",320,"9");

表T_Book中最后一条数据的FAuthorId字段值为9，但是在T_AUTHOR表中却没有主键值为9 的记录，也就是这条记录引用了不存在的作者。

2)不能约束删除T_AUTHOR中已经被T_Book表引用的记录。
如：我们执行下面的SQL语句删除T_AUTHOR表中的部分记录：

DELETE FROM T_AUTHOR WHERE FAge>30

虽然表T_Book中《Inside Curses》这本书还引用着badboy这个作者，但是这个作者仍然被删除了，这样同样造成了T_Book表中引用了T_AUTHOR表中不存在的记录。

如何防止数据表之间的关系被破坏？

外键约束机制可以解决这个问题，它允许指定一个表中的一个列的值是另外一个表的外键，即一个表中的一个列是引用另外一个表中的记录。

例如，可以设定T_Book 表中的FAuthorId 字段是一个依赖于T_AUTHOR表的FId列中存在的主键值。

在创建表时添加外键约束，其定义方式和复合主键类似，语法如下：

FOREIGN KEY 外键字段REFERENCES 外键表名(外键表的主键字段)

如：下面的SQL语句是添加了外键约束的，T_AUTHOR表和T_Book表的创建语句：

MYSQL、MSSQLServer：
CREATE TABLE T_AUTHOR(
FId VARCHAR(20) PRIMARY KEY,
FName VARCHAR(100),FAge INT,FEmail VARCHAR(20));

CREATE TABLE T_Book(
FId VARCHAR(20) PRIMARY KEY,
FName VARCHAR(100),FPageCount INT,
FAuthorId VARCHAR(20) ,
FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId));

Oracle：
CREATE TABLE T_AUTHOR(
FId VARCHAR2(20) PRIMARY KEY,
FName VARCHAR2(100),FAge NUMBER (10),
FEmail VARCHAR2(20));

CREATE TABLE T_Book(
FId VARCHAR2(20) PRIMARY KEY,
FName VARCHAR2(100),FPageCount NUMBER (10),
FAuthorId VARCHAR2(20) ,
FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId));

DB2：
CREATE TABLE T_AUTHOR(
FId VARCHAR(20) NOT NULL PRIMARY KEY,
FName VARCHAR(100),FAge INT,
FEmail VARCHAR(20));

CREATE TABLE T_Book(
FId VARCHAR(20) NOT NULL PRIMARY KEY,
FName VARCHAR(100),FPageCount INT,
FAuthorId VARCHAR(20) ,
FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId));

插入数据：

INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES("1","lily",20,"lily@cownew.com");
INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES("2","kingchou",23,"kingchou@cownew.com");
INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES("3","stef",28,"stef@cownew.com");
INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES("4","long",26,"long@cownew.com");
INSERT INTO T_AUTHOR(FId,FName,FAge,FEmail)VALUES("5","badboy",31,"badboy@cownew.com");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("1","About Java",300,"1");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("2","Inside Ruby",330,"2");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("3","Inside Curses",200,"5");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("4","Python InAction",450,"4");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("5","WPF Anywhere",250,"1");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("6","C# KickStart",280,"3");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("7","Compling",800,"1");
INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("8","Faster VB.Net",300,"5");

尝试向表中插入违反外键约束的数据，如：

INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("9","AboutWinCE",320,"9");

因为表T_AUTHOR 中没有主键值等于9 的记录，所以执行后系统会报出如下错误信息：

INSERT 语句与 FOREIGN KEY 约束"FK__T_Book__FAuthorI__38996AB5"冲突。该冲突发生于数据库"demo"，表"dbo.T_AUTHOR", column "FId"。

修改上面的SQL语句，使其FAuthorId字段的值为在表T_AUTHOR中存在的主键值：

INSERT INTO T_Book(FId,FName,FPageCount,FAuthorId)VALUES("9","AboutWinCE",320,"3");

同样，我们不能删除被T_Book表引用的T_AUTHOR表中的数据，比如我们想执行下面的SQL语句将作者long从T_AUTHOR表中删除：

DELETE FROM T_AUTHOR
WHERE FName=" badboy"

因为《Inside Curses》、《Faster VB.Net》这两本书的作者为主键值等于5的作者badboy，所以上面的SQL语句执行后数据库系统会报出如下的错误信息：

DELETE 语句与REFERENCE 约束"FK__T_Book__FAuthorI__38996AB5"冲突。该冲突发生于数据库"demo"，表"dbo.T_Book", column "FAuthorId"。

删除一个表中的数据时，若其他表中存在指向这些数据的外键关系，那么删除操作将会失败，除非将所有相关的数据都删除。
如：先执行下面的SQL 语句将作者long 的所有著作删除：

DELETE FROM T_Book
WHERE FAuthorId =5;

然后就可以执行下面的SQL语句将作者long从T_AUTHOR表中删除了：

DELETE FROM T_AUTHOR
WHERE FName="badboy"

若创建表时，未添加外键约束，则可使用ALTER TABLE语句添加外键约束，其语法与添加UNIQUE约束类似。
如：以ALTER TABLE 语句的方式添加外键约束：

ALTER TABLE T_Book
ADD CONSTRAINT fk_book_author
FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId)

现在可以删除T_AUTHOR表和T_Book表了：

DROP TABLE T_Book;
DROP TABLE T_AUTHOR;

注意：这里的删除顺序是首先删除T_Book，再删除T_AUTHOR，否则会因为违反外键约束而执行失败。

相关文章

  • 五、SQL–索引/约束⑥(外键约束)

    引出：当一些信息在表中重复出现时，就要考虑要将它们提取到新的表中，并在源表中引用新创建的中的数据。 如：很多作者都...

  • sql 添加索引，外键约束

    索引 外键约束

  • 面试积累之数据库篇（六）

    事务的4大属性：原子性，隔离性，一致性，持久性 sql完整性约束：主键约束唯一约束检查约束外键约束默认约束 sql...

  • 五、SQL–索引/约束⑤(主键约束)

    第一范式要求每张表都要有主键，因此主键约束是非常重要的，而且*主键约束是外键关联的基础条件。主键约束为表之间的关联...

  • 五、SQL–索引/约束④(check约束)

    CHECK约束会： 检查输入到记录中的值是否满足一个条件，如果不满足这个条件则对数据库做的修改不会成功。如，一个人...

  • 5 MySQL 命令

    [Toc] 1 禁用外键约束 禁用外键约束：SET FOREIGN_KEY_CHECKS=0 启用外键约束：set...

  • 五、SQL—索引/约束①

    在数据库系统中解决问题的技术就是索引与约束。索引用来提高数据的检索速度约束则用来保证数据的完整性 索引 索引是建立...

  • 第七章 SQL表之间的关系

    第七章 SQL表之间的关系 要在表之间强制执行引用完整性，可以定义外键。修改包含外键约束的表时，将检查外键约束。 ...

  • 五、SQL–索引/约束②(非空约束)

    在定义数据表的时候，默认情况下所有字段都是允许为空值的，如果需要禁止字段为空，那么就需要在创建表的时候显示指定。指...

  • SQL中增删外键约束

    1. 删除外键约束 查询出表中外键约束 删除约束alter table 表名 drop constraint 外键...

网友评论

    本文标题：五、SQL–索引/约束⑥(外键约束)

    本文链接：https://www.haomeiwen.com/subject/twijwhtx.html

    延伸阅读

    深度阅读

    • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

    栏目导航

    热点阅读

    软件测试

    SQL极简教程 · MySQL · MyBatis · JPA 技术笔记 教程 总结

    工作生活

    关于我们|服务条款|联系我们|五、SQL–索引/约束⑥(外键约束)|投稿指南|网站地图|RSS订阅|排版工具|手机版

    提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

    Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

    备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

    本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

    所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！