三、Sql Server 基础培训《进度3-是否使用外键（知识点学习）》

学习作业3：

问题1：你觉得外键有哪些适用情况？哪些不适用情况？

 

问题2：本次实战案例，由你来架构，你觉得有必要建立外键吗？

说明你的理由？

 

 

=====================================

=====================================

知识点：

1、建立外键的写法

让我们通过一个例子来解释外键。请看下面两个表：

"Persons" 表：

Id_P	LastName	FirstName	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" 表：

Id_O	OrderNo	Id_P
1	77895	3
2	44678	3
3	22456	1
4	24562	1

请注意，"Orders" 中的 "Id_P" 列指向 "Persons" 表中的 "Id_P" 列。

"Persons" 表中的 "Id_P" 列是主键。

"Orders" 表中的 "Id_P" 列是外键。

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

 

在 "Orders" 表，为 "Id_P" 列创建 FOREIGN KEY：

CREATE TABLE Orders

(

O_Id int NOT NULL PRIMARY KEY,

OrderNo int NOT NULL,

Id_P int FOREIGN KEY REFERENCES Persons(Id_P)

)

如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：

CREATE TABLE Orders

(

O_Id int NOT NULL,

OrderNo int NOT NULL,

Id_P int,

PRIMARY KEY (O_Id),

CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)

REFERENCES Persons(Id_P)

)

 

 

如果在 "Orders" 表已存在的情况下，为 "Id_P" 列创建 FOREIGN KEY 约束，请使用下面的 SQL：

ALTER TABLE Orders

ADD FOREIGN KEY (Id_P)

REFERENCES Persons(Id_P)

如果需要命名 FOREIGN KEY 约束，以及为多个列定义 FOREIGN KEY 约束，请使用下面的 SQL 语法：

ALTER TABLE Orders

ADD CONSTRAINT fk_PerOrders

FOREIGN KEY (Id_P)

REFERENCES Persons(Id_P)

 

 

撤销 FOREIGN KEY 约束

如需撤销 FOREIGN KEY 约束，请使用下面的 SQL：

ALTER TABLE Orders

DROP CONSTRAINT fk_PerOrders

下面是一个例子：

CREATE TABLE a

(

ID varchar(36) NOT NULL PRIMARY KEY

)

CREATE TABLE b

(

ID varchar(36) NOT NULL PRIMARY KEY

)

CREATE TABLE a_b

(

ID varchar(36) NOT NULL PRIMARY KEY,

aID varchar(36),

bID varchar(36),

FOREIGN KEY (aID) REFERENCES a(ID),

foreign key (bID) references b(ID)

ON DELETE NO ACTION

ON UPDATE NO ACTION /*还可接CASCADE,SET NULL,SET DEFAULT*/

)

NO ACTION:表示不执行任何操作，当删除主键表中数据时，如果外键表中有相应的数据，则主键表中的删除操作 失败，该选项为默认选项。

CASCADE : 表示级联操作，当删除主键表中数据时，外键表中相应的数据会被自动删除

SET NULL: 执行级联操作，并且将外键表中的所有数据都设置为空值。

SET DEFAULT :与SET NULL类似，外刍表中数据都设置为默认值。

 

===========================================

===========================================

2、是否有必要使用外键？ 为什么不用外键？

一、正方观点（需要使用外键）

理由1：数据完整性：由数据库自身保证数据一致性，完整性，更加可靠。如果只靠程序来控制，因为程序很难100％保证数据的完整性，而用外键即使在数据库服务器当机或者出现其他问题的时候，也能够最大限度的保证数据的一致性和完整性。

举例：数据库和前端应用是一对多的关系，假设现在开发了Ａ应用，A应用的程序代码会维护他那部分数据的完整性。然后，过了一段时间，系统决定新增功能，增加了Ｂ应用，B应用的开发有可能会影响到A应用的数据，或者影响A应用的数据表结构。很遗憾的是，Ａ和Ｂ两个应用是不同开发团队来做的。他们如何协调保证数据的完整性，而且一年以后如果又增加了C应用呢？

理由2：ER图可靠性：数据库ER图（数据库实体关系图）：有主外键的数据库设计可以增加ER图的可读性，这点在数据库设计时非常重要。

理由3：使设计更全面：外键在一定程度上说明的业务逻辑，会使设计周到具体全面。

理由4：级联性能未必最低：除非能证明触发器的性能和可维护性比外键更优，否则凭什么一定要否定外键呢？

二、反方观点（不需要使用外键）

 

理由1：程序逻辑：某些程序逻辑中，程序的逻辑已经足够保证完整性，我会在存储过程或包等地方做严谨的判断；

理由2：性能问题：这是很多人不喜欢用的关键原因，比如一个业务流水表，频繁插入数据，如果这个表身上有3外键，那么每次插入一条，就必须对这3个外键对应的3个表做相应的查找判断有无对应数据，如果这3个表也很大，那就这3个表的判断时间就很常，虽然外键指向的关联表的字段肯定是索引，但是我觉得很多时候，这样的判断本来就在程序里控制好了，通过外键再判断一次，就是降低性能；而且其实有的地方判不判断也无所谓的，但是用了外键，就必须化时间去判断，无论oracle内部多么优化外键对于数据的检索速度，它总是一个不小的消耗；

理由3：维护麻烦：很多公司的软件都是定制的，这种定制的东西，随意性相对较大，项目开发实施过程中，需要经常对表修修补补；还有就是业务逻辑有bug或者其他情况，需要经常手工维护数据，有错综复杂的外键关联着，很是麻烦；

理由4：外键定死了先后生成关系：外键定死了两个表之间数据的先后生成关系，最常见的是单据主从表，有的时候，在生成单据的时候，是先生成明细，再生成主表；如果钉死了外键，这个就没法实现；

理由5：当然有些关键的业务，确实需要外键；

==============================================

==============================================

3、大家设计数据库时使用外键吗？

观点分享1：互联网行业不推荐使用外键、传统行业推荐可以使用外键、外键有性能问题

外键是否采用看业务应用场景，以及开发成本的，大致列下什么时候适合，什么时候不适合使用：

观点1： 互联网行业应用不推荐使用外键： 用户量大，并发度高，为此数据库服务器很容易成为性能瓶颈，尤其受IO能力限制，且不能轻易地水平扩展；若是把数据一致性的控制放到事务中，也即让应用服务器承担此部分的压力，而引用服务器一般都是可以做到轻松地水平的伸缩；

观点2：传统行业：

1>.软件应用的人数有限，换句话说是可控的；

2>.数据库服务器的数据量也一般不会超大，且活跃数据有限；

综合2点，在传统行业，数据库服务器的性能不是问题，所以不用过多考虑性能的问题；另外，使用外键可以降低开发成本，借助数据库产品自身的触发器可以实现表与关联表之间的数据一致性和更新；最后一点，使用外键的方式，还可以做到开发人员和数据库设计人员的分工，可以为程序员承担更多的工作量；

观点3：为何说外键有性能问题：

1.数据库需要维护外键的内部管理；

2.外键等于把数据的一致性事务实现，全部交给数据库服务器完成；

3.有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；

4.外键还会因为需要请求对其他表内部加锁而容易出现死锁情况；

观点分享2：项目初期设计使用外键、后期业务运行去除外键

新手一枚，自己搞外包成长起来的，来说说看法，务喷。

之前搞一个外包是开发OA系统的，在系统设计之初，数据库的设计很规范的，大量使用外键，约束，符合范式要求。因为这样，我可以迅速的建立一个可靠性非常高的数据库结构，而不用让应用程序层去做过多的检查，谁都想写最少的代码，完成更多的功能。

所以，项目初始阶段，使用外键是必须的，而且是强烈推荐使用外键，数据库自带的约束，这样可以让你的业务架构迅速成型。

等项目的数据量越做越大，用户数越来越多的时候，那个时候，已经可以充分证明你的业务架构是正确的，这个时候你要是有性能瓶颈上的问题，完全可以把外键去除，转移到应用层实现。

总结：和我一样是新手的同学，不要被老司机的结论所蒙骗，他们一般都只说结果，不讲过程的。

过程就是，设计业务架构，系统初始之初，规规矩矩的使用外键，符合范式，待业务发展，确定架构本身没有问题，一些约束渐渐的平移到应用层实现。

结果就是，每个阶段都有不同的做法。根据实际情况去设计实现。

观点分享3：架构事务管理能力强、可以替代掉外键、应用服务器比数据库服务器便宜更容易扩展

因为很多答案对但是不精到，我再回答一个。

这实际上不是个数据库问题，而是个架构问题。

使用外键与否，看你使用的框架是否有足够的事务管理能力。

（其实不仅限于外键，还有一切可被替代的 db 高级特性）

如果足够，那么应该尽量避免。

理由很简单：app server 比 db server 便宜，而且拓展容易。

观点分享4：数据量大不适用外键、安全性可靠性要求高适用外键、高并发不适用外键

我想，这取决于数据库的用途、规模、架构，有外键，可以提高鲁棒性、健壮性，但是约束检验显然会拖慢速度。

规模上说，数据量大的不适合用外键，小的可以用；用途上安全性、可靠性很重要的就要用外键，否则可以不用。具体情况具体解决了，因为也有矛盾的时候，数据量极大，但是又要求高可靠，例如银行金融、芯片生产等，仍然需要外键的存在。可以通过SAN+RAID等硬件提升解决矛盾。要求高并发的情况下，并不适合外键，有的连关系数据库都不用了，甚至数据库都不用了。这类问题真没有绝对的答案，什么情况下该怎样做，只能是多想，多做了，错的多了，就懂了。

观点分享5：除非没有其他办法提升性能，建议严格使用外键

除非为了提高性能没有任何其他办法可以用了，否则不要取消外键。数据被错误覆写、错误数据进库（会引发不可预期的连锁反应）往往是比程序错误更难修复的错误，有时甚至不能修复我通常不仅加足外键，还狂加约束，一直被称为“约束狂人”。我写的代码在所有非性能关键路径上全是各种检查，Release版也保留。优化的话则从顶层入手、从整体入手。我做过这么多项目从来没有遇到什么问题。

观点分享6：性能遇到瓶颈时再去除外键、数据量用户量大可以不用外键

设计数据库时使用外键关联，甚至等到系统部署发布，在生产环境上仍然使用外键。。。毕竟，等到你发现用户量增长，数据库写入成为瓶颈的时候，完全可以删掉外键关联嘛。。。而且，因为之前的外键约束，也能尽早的发现应用系统的 Bug，从而在之后删掉外键关联的时候不用担心应用系统哪里做得不到位。

当然，如果用户量增长过快，或者是在一个用户量本身就很大的旧系统上新增业务功能，那就只能不用外键，老老实实审查代码了。

观点分享7：互联网应用中，有更好的选择，外键无用

外键在早期企业系统数据库设计里面比较多吧，本意是好了，帮程序员节省delete,update操作，实际上增加了潜规则，也增加了软件复杂度。互联网应用中，一般流量比较大，数据库当memecache用，大表+冗余字段，索引还建在外部用sphinx之类，基本上没有表和表的关联关系，外键无用

观点分享8：在应用层做防错检查、数据库不设置外键

做外键主要目的就是链接各表间的关系，防止直接操作导致数据紊乱，或者垃圾信息的产生。但目前各种后端框架发展到目前这种地步，在模型层已经可以处理好这些逻辑了，而模型层的意义本来就是避免对数据库进行直接操作。我目前在设计数据库的时候基本都不设置外键，而是在模型层删除处做检查处理，逻辑特别清晰。

观点分享9：小应用没必要用外键、大数据备份和清理用外键、逻辑要求精细用外键

外键好比保险绳，如果代码逻辑和数据库备份没有问题，设置不设置都一样。外键在数据库表中的存在，是为了约束数据库的一致性。不过我认为实际设计开发、运营维护过程中，应该没必要使用。外键更多的需要作用与海量数据库中，几百万几千万的数据备份和清理，需要外键。数据更清晰和整齐嘛，小应用和数据库，没必要使用吧。系统复杂，并出现海量数据的时候，那么，业务逻辑需要极为精细的分解为：数据逻辑和程序逻辑，并保持精细一致对应，这个时候，数据库管理员设置外键，保证数据库数据干净有效，这是ok的。简单就一句话，小系统，用户量或者数量不超过百万级别的，都没有必要介意是否使用外键。不用就好了。

观点分享10：不经常改的数据用外键、经常更改的数据不用外键

建议:一些常量的事情,不经常改的常数,请使用外键,来记录数据关系.而那些经常更改的,请使用更高层的逻辑来记录数据关系.

观点分享11：项目初期适用外键、互联网应用不适用外键、读取数据和外键无关

外键提供了一个数据完整性的工具,开发者可以以更轻便的方式解决问题。特别在初期阶段，项目架构以及资源成本的限制。外键可以大大提高开发效率及容错性。互联网应用因为数据量大及集群，导致外键在某些场景下带来不便。因此才会去选择性的删除外键来应对。外键主要在写业务上有影响。合理的设计中，写业务的一致性可以由数据库关系做到，而不是分布式，外键仍旧有存在的意义。对于读业务来说，本身就和外键没太大关系，特别是高并发场景下的各种优化特性。因此，我认为更应该从设计层关注领域特性，从设计上做到简单。我认为，撇开业务场景讨论技术本身就是没有意义的。还是要根据业务场景决定是否使用外键。

观点分享12：大数量用外键，写入速度慢

我做的一个写入很频繁，少量用户就会产生大量数据库数据条数的应用，在内存不是很充足的情况下，去掉外键一下子使写入速度很慢的一个瓶颈优化了十倍。之前业务相关的十多个表都使用了外键约束，并且有几张表数据量达到了百万级的数据。

观点分享13：系统表少和表关联简单适用外键、表数量多和表关联复杂不适用外键、同一模块使用外键、不同模块采用业务逻辑接口

最近也正在思考这样的问题，在小型单一数据库里面使用外键约束好像不会存在很大的问题，但是如果系统表的数量很多，而且很多都存在一个，或者多个表相关联的情况，外键的约束好像就有点吃力，个人觉得按照模块来拆分，同一模块中可以使用外键约束，不同模块的数据交互采用不同的接口来提供。

观点分享14：使用外键，数据迁移很困难

一般都是做应用层的逻辑控制，不建立实际外键，也就是说是通过程序的处理来实现系统外键的功能，因为外键很难维护，而且对于数据迁移来讲是很困难的，我们设计的数据库大部分表连自增长都没有，因为迁移起来太困难了

最后弱弱的问一句，你学会外键了吗？

学习作业3答案公布：

没有标准答案，智者见智，仁者见仁