MySql外键学习总结

mysql添加外键

为已经添加好的数据表添加外键:
语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(


对应的表的主键字段名);
例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id)
//FK_ID是外键的名称
/*
CREATE TABLE `tb_active` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `content` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `user_id_2` (`user_id`),
 CONSTRAINT `FK_ID` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
*/
 
删除外键
语法： ALTER TABLE table-name DROP FOREIGN KEY key-id;
例：   ALTER TABLE `tb_active` DROP FOREIGN KEY `FK_ID`
 
自动键更新和删除：
外键可以保证新插入的记录的完整性，但是，如果在REFERENCES从句中已命名的表删除记录会怎么样？


在使用同样的值作为外键的辅助表中会发生什么?
  
 很明显，那些记录也应该被删除，否则在数据库中就会有很多无意义的孤立记录，MYSQL可以通过向


FOREIGN KEY...REFERENCES修饰符添加一个ON DELETE 或ON UPDATE子句简化任务，它告诉了数据库在这


种情况如何处理孤立任务
 
 关键字     含义
 CASCADE    删除包含与已删除键值有参照关系的所有记录
 SET NULL   修改包含与已删除键值有参照关系的所有记录，使用NULL值替换（只能用于已标记为NOT 


NULL的字段）
 RESTRICT   拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，


也是最安全的设置)
 NO ACTION  啥也不做
 
 请注意，通过ON UPDATE 和 ON DELETE规则，设置MYSQL能够实现自动操作时，如果键的关系没有设置


好，可能会导致严重的数据破坏，
 例如：如果一系列的表通过外键关系和ON DELETE CASCADE 规则连接时，任意一个主表的变化都会导致


甚至只和原始删除有一些将要联系的记录在没有警告的情况被删除，所以，我们在操作之前还要检查这


些规则的，操作之后还要再次检查.
 
添加外键
alter table locstock add foreign key locstock_ibfk2(stockid) references product(stockid)
locstock 为表名, locstock_ibfk2 为外键名 第一个括号里填写外键列名, product为表名,第二个括号


里是写外键关联的列名
 
删除外键 
alter table locstock drop foreign key locstock_ibfk2
 
查看表有哪些外键
show create table locstock
 
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)  
    REFERENCES tbl_name (index_col_name, ...)  
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]  
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]  
 
         所有tables必须是InnoDB型 ，它们不能是临时表。
·         在引用表中，必须有一个索引，外键列以同样的顺序被列在其中作为第一列。这样一个索引


如果不存在，它必须在引用表里被自动创建。
·         在引用表中，必须有一个索引，被引用的列以同样的顺序被列在其中作为第一列。
·         不支持对外键列的索引前缀。这样的后果之一是BLOB和TEXT列不被包括在一个外键中， 这


是因为对这些列的索引必须总是包含一个前缀长度。
·         如果CONSTRAINTsymbol 被给出，它在数据库里必须是唯一的。如果它没有被给出，InnoDB


自动创建这个名字。
========

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的主键，但必须是子表的主键。（简单的说就是，如果一个字段是某个表的外键时


，那么该字段必须是主键）
因为你前面就理解错了，所以这句话本身就是错的。对于从表来说，外键不一定需要作为从表的主键，


外键也不一定是外表的主键，外表的唯一键就可以作 为从表的外键。


再给一张图以帮助理解
========

mysql外键(FOREIGN KEY)的简单使用

一、基本概念
1、MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动


为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有


的参照表中进行明确地索引，InnoDB不能自动地创建索引。
2、外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记


录与另一个表的多条记录连接。
3、如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根


据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。
4、外键的使用条件
① 两个表必须是InnoDB表，MyISAM表暂时不支持外键
② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版


本则需要显式建立；
③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以


，而int和char则不可以；
5、外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。
二、使用方法
1、创建外键的语法：
外键的定义语法：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自


动生成一个名字。
ON DELETE、ON UPDATE表示事件触发限制，可设参数：
① RESTRICT（限制外表中的外键改动，默认值）
② CASCADE（跟随外键改动）
③ SET NULL（设空值）
④ SET DEFAULT（设默认值）
⑤ NO ACTION（无动作，默认的）
2、示例
1）创建表1
create table repo_table(
repo_id char(13) not null primary key,
repo_name char(14) not null)
type=innodb;
创建表2
mysql> create table busi_table(
    -> busi_id char(13) not null primary key,
    -> busi_name char(13) not null,
    -> repo_id char(13) not null,
    -> foreign key(repo_id) references repo_table(repo_id))
-> type=innodb;
2）插入数据
insert into repo_table values("12","sz"); //success
insert into repo_table values("13","cd"); //success
insert into busi_table values("1003","cd", "13"); //success
insert into busi_table values("1002","sz", "12"); //success
insert into busi_table values("1001","gx", "11"); //failed,提示：
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 


(`smb_man`.`busi_table`, CONSTRAINT `busi_table_ibfk_1` FOREIGN KEY (`repo_id`) REFERENCES 


`repo_table` (`repo_id`))
3）增加级联操作
mysql> alter table busi_table
    -> add constraint id_check
    -> foreign key(repo_id)
    -> references repo_table(repo_id)
    -> on delete cascade
    -> on update cascade;
-----
ENGINE=InnoDB DEFAULT CHARSET=gb2312； //另一种方法，可以替换type=innodb;
3、相关操作
外键约束（表2）对父表（表1）的含义:
    在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时，父表的行


为取决于：在定义子表的外键时指定的on update/on delete子句。
关键字
含义
CASCADE
删除包含与已删除键值有参照关系的所有记录
SET NULL
修改包含与已删除键值有参照关系的所有记录，使用NULL值替换(只能用于已标记为NOT NULL的字段)
RESTRICT
拒绝删除要求，直到使用删除键值的辅助表被手工删除，并且没有参照时(这是默认设置，也是最安全的


设置)
NO ACTION
啥也不做
4、其他
在外键上建立索引:
index repo_id (repo_id),
foreign key(repo_id) references repo_table(repo_id))
========

链接

【1】 w3school关于mysql的专题讲解
http://www.w3school.com.cn/sql/sql_foreignkey.asp
【2】 MySQL C API programming tutorial
http://zetcode.com/tutorials/mysqlcapitutorial/
【3】 对外键的使用示例，很不错
http://hi.baidu.com/wangzhiqing999/blog/item/08761705954e18c4267fb523.html
http://www.cppblog.com/wolf/articles/69089.html
【4】 mysql官网英文网站
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
中文网站
http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html
【5】 对外键讲解的比较全面，可以一读
http://www.xiaoxiaozi.com/2009/07/12/1158/
http://feidaodalian.iteye.com/blog/550179