mysql外键
博客专区 > wayhk 的博客 > 博客详情
mysql外键
wayhk 发表于3年前
mysql外键
  • 发表于 3年前
  • 阅读 42
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

最近自学 数据库MySQL,然后有个疑问,一直不得其解,查询了相关资料,最后还是没有解决。

     我的疑问是 "使用外键约束" ,然后我对 "外键" 这个词不是很理解,查询相关资料都是讲一些术语,说外键的主要作用是:保持数据的一致性、完整性。听得我是一头雾水。

关于外键,我有自己的一些理解,但是不晓得是否正确,举个例子来表达我的看法:假如现在需要建立一个表,一个什么样的表呢?一个班级的学生个人信息表:

\

所以在设计的时候,就给表1添加一个外键,这个外键就是表2中的学号字段,那么这样表1就是主表,表2就是子表。所以结合2张表就能保持数据的一致性、完整性(估计就是还原成原来的那张大表吧)。
     借着这个例子再谈谈外键的一些事项:
1、表1可以有一个或者多个外键,也可以没有。(如果表1有多个外键可不可以是这样的情况,表2中的多个字段是表1的外键;或者说表1的多个外键是在多个表中)
2、这个外键可以不是表1的主键,但必须是子表的主键。(简单的说就是,如果一个字段是某个表的外键时,那么该字段必须是主键)

以上就是我个人对外键的理解。

----------------------------------------解---答---纠---正-----------------------------------------

什么是外键

+-------+   ref   +-------+
|  sub  | ------> |  main |
+-------+         +-------+

从表(sub)的某列引用(ref)主表(main)的某列的值。比如学生表有个学生编号(sid),分数表中的学生列(stu)引用学生表的学 生编号,此时对于分数表的 stu 来说,学生表的 sid 就是外键。从表也叫外键表,主表也叫主键表、外表,列也叫字段。

所以在设计的时候,就给表1添加一个外键,这个外键就是表2中的学号字段,那么这样表1就是主表,表2就是子表

你的主从关系理解颠倒了。你的图中,表1的确是主表,表2是子表,但不是叫做给表1添加一个外键,而是给表2添加一个外键,表2中的学号 字段就叫外键,它是表1学号字段的主键。你可以这样说:表1的学号字段是表2的外键。

外键用来干什么

你贴的图片已经解释了。为了一张表记录的数据不要太过冗余。这和软件工程的模块化思想差不多类似,只不过在数据库中是对表关系进行解耦,尽量让表 记录的数据单一化。就如你贴的图片中,把成绩和学生信息放在一张表中就太冗余了,成绩完全可以以学生的id作为区分标识。

为什么说外键能保持数据的一致性、完整性

你想想,你的图中的第一章表分割成了表1和表2,表2的学号引用了表1的学号字段作为外键,如果不建立外键,只是和表1一样单纯性 地设立一个学号字段,那么和建立外键有什么区别呢?

比如表1中张三的学号为20140900001,那么我在表2中插数据的时候在学号字段插20140900001来记录张三的成绩不也是做到了表 的解耦了吗?

这里存在的问题是,在不设置外键的情况下,表2的学号字段和表1的学号字段是没有关联的。只是你自己认为他们有关系而已,数据库并 不认为它俩有关系。也就是说,你在表2的学号字段插了一个值(比如20140999999),但是这个值在表1中并没有,这个时候,数据库还是允 许你插入的,它并不会对插入的数据做关系检查。然而在设置外键的情况下,你插入表2学号字段的值必须要求在表1的学号字段能找到。 同时,如果你要删除表1的某个学号字段,必须保证表2中没有引用该字段值的列,否则就没法删除。这就是所谓的保持数据的一致性和完整性。你想,如 果表2还引用表1的某个学号,你却把表1中的这个学号删了,表2就不知道这个学号对应的学生是哪个学生。数据的一致性还包括数据类型的一致性(这 个见下面就知道了)。

外键的使用规则

从表的字段必须与外键类型相同(如上,分数表 stu 的类型必须和学生表 sid 的类型相同,比如都是 int(10) 类型)外键必须是主表的唯一键(如上,学生表 sid 是主键,而主键是唯一的,所以可以作为分数表 stu 的外键)有关联的字段(如上,分数表之所以使用学生表的 sid 是因为两者有关联,分数表记录的是学生的分数,而学生可以用 sid 来唯 一标识)避免使用复合键(也就是说从表可以同时引用多个外表的字段作为一个外键,一般不推荐这种做法)

你的问题

如果表1有多个外键可不可以是这样的情况,表2中的多个字段是表1的外键;或者说表1的多个外键是在多个表中

都可以。因为表1的外键不一定是表2的主键,也可以是唯一键(UNIQUE)。比如表2有个主键 A,有个唯一键 B,表1两个字段 A' 和 B’ 分别引用表2的 A 和 B,这就是多对多的关系了。再或者表2主键 A,表3主键 B,表1的两个字段 A' 和 B' 分别引用表2的 A 和表3 的 B。

这个外键可以不是表1的主键,但必须是子表的主键。(简单的说就是,如果一个字段是某个表的外键时,那么该字段必须是主键)

因为你前面就理解错了,所以这句话本身就是错的。对于从表来说,外键不一定需要作为从表的主键,外键也不一定是外表的主键,外表的唯一键就可以作 为从表的外键。

再给一张图以帮助理解

\

外键的作用:

保持数据一致性,完整性,主要目的是控制存储在外键表中的数据。 使两张表形成关联,外键只能引用外表中的列的值!

例如:

a b 两个表

a表中存有客户号,客户名称

b表中存有每个客户的订单

有了外键后

你只能在确信b  表中没有客户x的订单后,才可以在a表中删除客户x

建立外键的前提:  本表的列必须与外键类型相同(外键必须是外表主键)。

指定主键关键字: foreign  key(列名)

引用外键关键字: references  <外键表名>(外键列名)

事件触发限制: on delete和on  update , 可设参数cascade(跟随外键改动), restrict(限制外表中的外键改动),set Null(设空值),set  Default(设默认值),[默认]no action

例如:

outTable表 主键 id 类型  int

创建含有外键的表:

create table  temp(

id int,

name char(20),

foreign key(id)  references outTable(id) on delete cascade on update cascade);

说明:把id列 设为外键  参照外表outTable的id列 当外键的值删除 本表中对应的列筛除 当外键的值改变 本表中对应的列值改变。

子表,父表的定义:  拥有外键的表是子表。主键被其它表引用的表是父表。
换句话说:因为父表的标识被很多个子表中的记录引用,所以叫父表。
拥有外键关系,并且可以随便删除数据,不影响其它表的数据的那个表叫子表。

使用的时候谁做为谁的外键,主要从以下两点考虑:
          1/,删除是如何相互影响的,删除记录受约束的那个是父表,不受约束的那个是子表;
         2/,记录必须先存在的是父表;

两种用途:
1/, 最常用的一种:  减少重复数据.表A中拥有外键,表B的数据基本是不允许删除的.这时选择对 INSERT 和 UPDATE  强制关系即可.
2/,其次,是增加一个从属表.  如果表A删除一条记录时,表B中也随着删除一条相关联的记录,那么外键关系中,表A的主键是表B的外键。这种关系,实际上表B是表A的从属表(即表A是父表),选择对  INSERT 和 UPDATE  强制关系时,如果向表B中插入数据,表A中必须已经存在对应的记录。选择级联删除相关的字段时,删除表A中的一条记录,就会删除对应的表B中的一条记录。

今天有朋友问我"外键的作用是什么"

当朋友问我外键的作用是什么时,我也愣了一下,平常都是在这么用,还没有真正的总结过,外分键的作用呢.下面,我总结了一下外键的作用:

外键 (FK)  是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中,可创建两个表之间的链接。这个列就成为第二个表的外键。

FOREIGN KEY  约束的主要目的是控制存储在外键表中的数据,但它还可以控制对主键表中数据的修改。例如,如果在 publishers 表中删除一个出版商,而这个出版商的 ID 在  titles 表中记录书的信息时使用了,则这两个表之间关联的完整性将被破坏,titles 表中该出版商的书籍因为与 publishers  表中的数据没有链接而变得孤立了。FOREIGN KEY   约束防止这种情况的发生。如果主键表中数据的更改使之与外键表中数据的链接失效,则这种更改是不能实现的,从而确保了引用完整性。如果试图删除主键表中的 行或更改主键值,而该主键值与另一个表的  FOREIGN KEY 约束值相关,则该操作不可实现。若要成功更改或删除 FOREIGN KEY   约束的行,可以先在外键表中删除外键数据或更改外键数据,然后将外键链接到不同的主键数据上去。

外键是用来控制数据库中数据的数据完整性的

就是当你对一个表的数据进行操作

和他有关联的一个或更多表的数据能够同时发生改变

这就是外键的作用

[精] 谈谈外键

外键 (FK)  是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中,可创建两个表之间的链接。这个列就成为第二个表的外键。

FOREIGN KEY  约束的主要目的是控制存储在外键表中的数据,但它还可以控制对主键表中数据的修改。例如,如果在 publishers 表中删除一个出版商,而这个出版商的 ID 在  titles 表中记录书的信息时使用了,则这两个表之间关联的完整性将被破坏,titles 表中该出版商的书籍因为与 publishers  表中的数据没有链接而变得孤立了。FOREIGN KEY   约束防止这种情况的发生。如果主键表中数据的更改使之与外键表中数据的链接失效,则这种更改是不能实现的,从而确保了引用完整性。如果试图删除主键表中的 行或更改主键值,而该主键值与另一个表的  FOREIGN KEY 约束值相关,则该操作不可实现。若要成功更改或删除 FOREIGN KEY   约束的行,可以先在外键表中删除外键数据或更改外键数据,然后将外键链接到不同的主键数据上去。

外键是用来控制数据库中数据的数据完整性的

就是当你对一个表的数据进行操作

和他有关联的一个或更多表的数据能够同时发生改变

这就是外键的作用


外键是数据库一级的一个完整性约束,就是数据库基础理论书中所说的“参照完整性”的数据库实现方式。

外键属性当然是可以去掉的,如果你不想再用这种约束,对编程当然不会有什么影响,但相应的录入数据的时候就不对录入的数据进行“参照完整性”检查了。

例如有两个表

A(a,b) :a为主键,b为外键(来自于B.b)

B(b,c,d) :b为主键

如果我把字段b的外键属性去掉,对编程没什么影响。

如上面,A中的b要么为空,要么是在B的b中存在的值,有外键的时候,数据库会自动帮你检查A的b是否在B的b中存在。

1、外建表达的是参照完整性:这是数据固有的,与程序无关。因此,应该交给DBMS来做。

2、 使用外建,简单直观,可以直接在数据模型中体现,无论是设计、维护等回有很大的好处,特别是对于分析现有的数据库的好处时非常明显的--前不久我分析了一 个企业现有的数据库,里面的参照完整性约束有的是外键描述,有的是用触发器实现,感觉很明显。当然,文档里可能有,但是也可能不全,但是外键就非常明显和 直观。

3、既然我们可以用触发器或程序完成的这个工作(指参照完整性约 束),DBMS已经提供了手段,为什么我们要自己去做?而且我们做的应该说没有RDBMS做得好。实际上,早期的RDBMS并没有外键,现在都有了,我认 为数据库厂商增加这个功能是有道理的。从这个角度来说,外键更方便。

4、关于方 便,根据我带项目的情况来看,程序员确实有反映,主要是在调试时输入数据麻烦:如果数据可以违反参照完整性,那么就是说参照完整性本身就不对名誉业务冲 突,此时也不应该用触发期货程序实现;否则,说明数据是错误的,根本就不应该进入数据库!而且,这也应该是测试系统的一个内容:阻止非法数据。实际上,前 台程序应该对这种提交失败做出处理。数据是企业的而非程序的,储程序要尽量与数据分离,反之亦然。

最后说一下,建键几个原则:

1、 为关联字段创建外键。

2、 所有的键都必须唯一。

3、避免使用复合键。

4、外键总是关联唯一的键字段。

外键的作用?  

外键是数据库一级的一个完整性约束,就是数据库基础理论书中所说的“参照完整性”的数据库实现方式。

外键属性当然是可以去掉的,如果你不想再用这种约束,对编程当然不会有什么影响,但相应的录入数据的时候就不对录入的数据进行“参照完整性”检查了。

例如有两个表

A(a,b) :a为主键,b为外键(来自于B.b)

B(b,c,d) :b为主键

如果我把字段b的外键属性去掉,对编程没什么影响。

如上面,A中的b要么为空,要么是在B的b中存在的值,有外键的时候,数据库会自动帮你检查A的b是否在B的b中存在。

1、外建表达的是参照完整性:这是数据固有的,与程序无关。因此,应该交给DBMS来做。

2、 使用外建,简单直观,可以直接在数据模型中体现,无论是设计、维护等回有很大的好处,特别是对于分析现有的数据库的好处时非常明显的--前不久我分析了一 个企业现有的数据库,里面的参照完整性约束有的是外键描述,有的是用触发器实现,感觉很明显。当然,文档里可能有,但是也可能不全,但是外键就非常明显和 直观。

3、既然我们可以用触发器或程序完成的这个工作(指参照完整性约 束),DBMS已经提供了手段,为什么我们要自己去做?而且我们做的应该说没有RDBMS做得好。实际上,早期的RDBMS并没有外键,现在都有了,我认 为数据库厂商增加这个功能是有道理的。从这个角度来说,外键更方便。

4、关于方 便,根据我带项目的情况来看,程序员确实有反映,主要是在调试时输入数据麻烦:如果数据可以违反参照完整性,那么就是说参照完整性本身就不对名誉业务冲 突,此时也不应该用触发期货程序实现;否则,说明数据是错误的,根本就不应该进入数据库!而且,这也应该是测试系统的一个内容:阻止非法数据。实际上,前 台程序应该对这种提交失败做出处理。数据是企业的而非程序的,储程序要尽量与数据分离,反之亦然。

最后说一下,建键几个原则:

1、 为关联字段创建外键。

2、 所有的键都必须唯一。

3、避免使用复合键。

4、外键总是关联唯一的键字段。


这个帖子很牛:

http://www.itpub.net/viewthread.php?tid=1313696&extra=&page=1

我的观点是,外 键在初始阶段能加的都加上,只有迫不得已的时候才disable或drop掉。遇到性能瓶颈的时候,尽量采用其它方式调优,而不要轻易牺牲掉外键。有外键 约束的时候,写程序的确会有约束,但从直觉上说这种约束一定程度上揭示了设计或实现上不合理的地方。带着外键写出来的应用更倾向于严谨。产品上线之前如果 确实需要通过牺牲外键达到性能上的优化,再捡相对不重要的外键废弃掉,同时要把这个document下来,下次遇到数据不一致问题的时候,是个线索。两点 说明:1.  我们在做的一个项目确实是小项目。  2. 我得承认我最近三年开发都不用关系型数据库,貌似 no sql那么nb的key-value  pair存数据,其实这三年在持久层上很多纠结。如果我说的不对,请指正!

下面引用一些有见地的想法:

× 支持外键的:

1. 你的程序再严谨也有可能出现BUG;你自己判断不如交给数据库判断,它做得又快又好。
    大多数人的程序没有考虑并发问题。一旦考虑了就得手工加锁,效率很低。
   数据可能绕过你的应用程序进入数据库。
2.  性能问题:难道你自己做就没有开销?
   一个外键判断分摊到事务级别,开销可以忽略,用户完全没有察觉。
    如果是批量导入数据,可以先暂时屏蔽外键,事后用NOVALIDATE选项快速恢复,前提是你的数据是干净的。

也有人提到了如果100张表可能需要建立300个约束,导致性能太差。
我要说的仍然是,是否这300个外键约束都是业务必须的,如果是,没有办法这就是必须要加的,如果不是,那么大可不必在所有的地方都增加外键。
如果在程序中仅对其中的5、6张表的10来个外键约束进行判断,然后和数据库中的300个外键去比较,并评价Oracle的外键性能太差,恐怕是有失公允的。

× 反对外键的:

的 确外键在大系统中用的很少,在开发初级,设计数据库的时候一般会加入外键,以保证系统设计的完整性和业务需求的完整性,也便于开发人员了解业务规则,在程 序中加以控制,很多大系统在系统稳定后,会逐步将外键去掉,以保证性能,将太多的功能强加于数据库,虽然说数据库很强大,但是毕竟很多人不信任数据库的能 强大到什么都能干的地步。所以在一个大系统中外键见的少也不足为奇,小系统就无所谓了,用不用外键取决于设计人员,这样的系统也随处可见。

另引用一篇:

引自http://blog.csdn.net/neusoft_lkz/archive/2009/07/21/4366668.aspx

数据库设计是否需要外键。这里有两个问题:一个是如何保证数据库数据的完整性和一致性;二是第一条对性能的影响。
正方观点:
1,由数据库自身保证数据一致性,完整性,更可靠,因为程序很难100%保证数据的完整性,而用外键即使在数据库服务器当机或者出现其他问题的时候,也能够最大限度的保证数据的一致性和完整性。
eg:数据库和应用是一对多的关系,A应用会维护他那部分数据的完整性,系统一变大时,增加了B应用,A和B两个应用也许是不同的开发团队来做的。他们如何协调保证数据的完整性,而且一年以后如果又增加了C应用呢?  
2,有主外键的数据库设计可以增加ER图的可读性,这点在数据库设计时非常重要。
3,外键在一定程度上说明的业务逻辑,会使设计周到具体全面。
反方观点:
1,可以用触发器或应用程序保证数据的完整性
2,过分强调或者说使用主键/外键会平添开发难度,导致表过多等问题
3,不用外键时数据管理简单,操作方便,性能高(导入导出等操作,在insert,    update,   delete    数据的时候更快)
eg: 在海量的数据库中想都不要去想外键,试想,一个程序每天要insert数百万条记录,当存在外键约束的时候,每次要去扫描此记录是否合格,一般还不止一个 字段有外键,这样扫描的数量是成级数的增长!我的一个程序入库在3个小时做完,如果加上外键,需要28个小时!

结论:
1,在大型系统中(性能要求不高,安全要求高),使用外键;在大型系统中(性能要求高,安全自己控制),不用外键;小系统随便,最好用外键。
2,用外键要适当,不能过分追求
3,不用外键而用程序控制数据一致性和完整性时,应该写一层来保证,然后个个应用通过这个层来访问数据库。


数据库中主键和外键的设计原则

http://www.cnblogs.com/tianyamoon/archive/2008/04/02/1134394.html

主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。

必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个设计过程的症结所在。一旦将所设计的数据库用于了生产环境,就很难对这些键进行修改,所以在开发阶段就设计好主键和外键就是非常必要和值得的。

主键:

关系数据库依赖于主键---它是数据库物理模式的基石。主键在物理层面上只有两个用途:

1. 惟一地标识一行。

2. 作为一个可以被外键有效引用的对象。

基于以上这两个用途,下面给出了我在设计物理层面的主键时所遵循的一些原则:

1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据,并抱怨它没有什么用处,那就证明它的主键设计地很好。

2. 主键应该是单列的,以便提高连接和筛选操作的效率。

注:使用复合键的人通常有两个理由为自己开脱,而这两个理由都是错误的。其一是主键应当具有实际意义,然而,让主键具有意义只不过是给人为地破坏数据库提供了方便。其二是利用这种方法可以在描述多对多关系的连接表中使用两个外部键来作为主键,我也反对这种做法,理由是:复合主键常常导致不良的外键,即当连接表成为另一个从表的主表,而依据上面的第二种方法成为这个表主键的一部分,然,这个表又有可能再成为其它从表的主表,其主键又有可能成了其它从表主键的一部分,如此传递下去,越靠后的从表,其主键将会包含越多的列了。

3. 永远也不要更新主键。实际上,因为主键除了惟一地标识一行之外,再没有其他的用途了,所以也就没有理由去对它更新。如果主键需要更新,则说明主键应对用户无意义的原则被违反了。

注:这项原则对于那些经常需要在数据转换或多数据库合并时进行数据整理的数据并不适用。

4. 主键不应包含动态变化的数据,如时间戳、创建时间列、修改时间列等。

5. 主键应当有计算机自动生成。如果由人来对主键的创建进行干预,就会使它带有除了惟一标识一行以外的意义。一旦越过这个界限,就可能产生认为修改主键的动机,这样,这种系统用来链接记录行、管理记录行的关键手段就会落入不了解数据库设计的人的手中。


共有 人打赏支持
粉丝 2
博文 37
码字总数 17775
×
wayhk
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: