mysql基础 - 外键 - 《数据库》

在实际的项目开发中，我们并不设置物理的外键，也就是使用FOREIGN KEY将多张有关联的表进行关联，因为MyISAM不能使用外键以及数据类型等条件的限制，所以我们经常使用的是逻辑的外键，也就是删除主表的数据的时候，要使用事务，连同有关联的子表的记录也一同删除。

外键概念：
外键和主键一样也是索引的一种。InnoDB引擎才允许使用外键,外键必须建立索引(子表在创建外键的时候也会自动创建对应的索引)。
键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接
如果需要更好的性能，并且不需要完整性检查，可以选择使用MyISAM表类型，如果想要在MySQL中根据参照完整性来建立表并且希望在此基础上保持良好的性能，最好选择表结构为innoDB类型。

外键的使用条件:
① 两个表必须是InnoDB表，MyISAM表暂时不支持外键
② 外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；参照列也必须建立索引。
③ 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；同时数字的长度或是否有符号位必须相同；而字符的长度可以不同；

外键好处：
为了避免数据的冗余，经常要创建主从表，外键就可以使得两张表关联，（比如成绩和学生信息放在一张表中就太冗余了，成绩完全可以以学生的id作为区分标识)
保证数据的一致性(表一是学号，表二是成绩。你插入表2成绩表中外键学号字段的值必须要求在表1的学号字段能找到。同时，如果你要删除表1的某个学号字段，必须保证表2中没有引用该字段值的列，否则就没法删除。（除非设置级联操作）这就是所谓的保持数据的一致性和完整性)
和实现一些级联操作(如果要在父表中更新或者删除一行，并且在子表中也有一行或者多行匹配):

此时子表的操作有5个选择：（也叫外键约束）

CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。在两个表之间，你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。
SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词，这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。
NO ACTION: 在ANSI SQL-92标准中，NO ACTION意味这不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新主要键值的企图不被允许进行（Gruber, 掌握SQL, 2000:181）。 InnoDB拒绝对父表的删除或更新操作。
RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样，删除ON DELETE或ON UPDATE子句。（一些数据库系统有延期检查，并且NO ACTION是一个延期检查。在MySQL中，外键约束是被立即检查的，所以NO ACTION和RESTRICT是同样的）。
SET DEFAULT: 这个动作被解析程序识别，但InnoDB拒绝包含ON DELETE SET DEFAULT或ON UPDATE SET DEFAULT子

句的表定义。

外键的定义语法：

 [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,系统会自动添加外键约束名：表名_ibfk_n(表示第n个外键约束)可以通过这个名字删除外键。 ON DELETE、ON UPDATE表示事件触发限制，可设参数：
① RESTRICT（限制外表中的外键改动，主表的改动不影响子表的外键。默认值）
② CASCADE（跟随外键改动，从父表删除或更新自动删除或跟新子表中匹配的行）
③ SET NULL（设空值，从父表删除或跟新行，自动设置子表中的外键列为null,外键列不能指定not null）
④ SET DEFAULT（设默认值）
⑤ NO ACTION（无动作，默认的，同RESTRICT）

2、示例
创建数据库：
Create database test;
创建两个表，其中第一个表的”id”是第二个表（userinfo）的外键：

CREATE TABLE `user` (
      `id` int(4) NOT NULL,
      `sex` enum('f','m') DEFAULT NULL,
       PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    CREATE TABLE `userinfo` (
      `sn` int(4) NOT NULL AUTO_INCREMENT,
      `userid` int(4) NOT NULL,
      `info` varchar(20) DEFAULT NULL,
      PRIMARY KEY (`sn`),
      KEY `userid` (`userid`),
      CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

外键绑定关系(外键约束)这里使用了“ ON DELETE CASCADE ” “ON UPDATE CASCADE”，意思是如果外键对应数据被删除或者更新时，将关联数据完全删除或者相应地更新（联级操作）

插入测试数据：

INSERT INTO `user` (`id`,`sex`) VALUES ('1', 'f'),  ('2', 'm'), ('3', 'f');
    INSERT INTO `userinfo` (`sn`,`userid`,`info`)
    VALUES  ('1', '1', '2005054dsf'),
            ('2', '1', 'fdsfewfdsfds'),
            ('3', '1', 'gdsgergergrtre'),
            ('4', '2', 'et34t5435435werwe'),
            ('5', '2', '435rtgtrhfghfg'),
            ('6', '2', 'ret345tr4345'),
            ('7', '3', 'fgbdfvbcbfdgr'),
            ('8', '3', '45r2343234were'),
            ('9', '3', 'wfyhtyjtyjyjy');
    测试联级删除：
    delete from `user` where `id`='2';
    user表中id=2的数据会被删除；
    userinfo表中userid为2的那三条数据也被删除；
    联级更新：
    user表中原来id为1的数据记录更改为id为4：update user set id=4 where id='1';
    userinfo表中的userid=1的也更改为4

    删除外键：删除userinfo的外键
    alter table ss_accesscode drop foreign key userinfo_ibfk_1（外键约束名称）