MySql -- foreign key 外键

5、foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据；

现在我们创建两个测试表：

-- 用户主表
CREATE TABLE `test`.`user`(  
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `loginName` VARCHAR(18) NOT NULL,
  `loginPwd` VARCHAR(11),
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

-- 信息附表
CREATE TABLE `test`.`info`(  
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` INT(11) NOT NULL,
  `mobile` VARCHAR(11),
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

 其中我们信息附表的userId应该从用户主表中来，防止添加信息附表后，找不到对应的用户。

 或者在删除用户主表时，导致信息附表数据直接没法对应，出现死数据。

 这时，我们就需要用到 foreign key 来保证数据在操作过程中的完整性。

 

创建外键

1.两个表必须是InnoDB表，MyISAM表暂时不支持外键
2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立；
3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

/*
 * 在需要外键约束的地方添加 FOREIGN KEY (`userId`) REFERENCES `info`(`id`)即可
 * 但在mysql会默认添加一个外键名 格式 info_ibfk_n ...
 * 外键名主要用于维护外键约束
 */
CREATE TABLE `test`.`info`(  
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` INT(11) UNSIGNED NOT NULL,
  `mobile` VARCHAR(11),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`userId`) REFERENCES `user`(`id`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

/*
 * 如果需要命名 FOREIGN KEY 约束，
 * 则可以使用 CONSTRAINT 约束名称 FOREIGN KEY (附表字段) REFERENCES 主表(外键字段)，
 * 这样就能新建一个fk_userId的外键。
 */
CREATE TABLE `test`.`info`(  
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `userId` INT(11) UNSIGNED NOT NULL,
  `mobile` VARCHAR(11),
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_userId` FOREIGN KEY (`userId`) REFERENCES `user`(`id`)
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;·

 

修改外键

-- 在已经存在表的情况下可以直接使用下面方法进行添加外键
ALTER TABLE `info` ADD FOREIGN KEY (`userId`) REFERENCES `user`(`id`);

-- 命名 FOREIGN KEY 约束
ALTER TABLE `info` ADD CONSTRAINT `fk_userId` FOREIGN KEY (`userId`) REFERENCES `user`(`id`);

 

撤销外键

-- 撤销 FOREIGN KEY 约束
ALTER TABLE `info` DROP FOREIGN KEY `fk_userId`;

 

外键约束 FOREIGN KEY 测试

-- 我们现在在主表中添加三个用户
INSERT INTO `user`(`loginName`,`loginPwd`) VALUES('lxw','123456');
INSERT INTO `user`(`loginName`,`loginPwd`) VALUES('wbkj','000000');
INSERT INTO `user`(`loginName`,`loginPwd`) VALUES('kmaa','111111');
SELECT * FROM `user`;

结果：
    id  loginName  loginPwd  
------  ---------  ----------
     1  lxw        123456    
     2  wbkj       000000    
     3  kmaa       111111   

-- 现在我们再在附表中添加数据
INSERT INTO `info`(`userId`,`mobile`) VALUES('1','15288888888');
结果：共 1 行受到影响

INSERT INTO `info`(`userId`,`mobile`) VALUES('4','15288888888');
结果：
错误代码： 1452
Cannot ADD OR UPDATE a child ROW: a FOREIGN KEY CONSTRAINT fails (`test`.`info`, CONSTRAINT `info_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`))
因为user表中不存在 4 这个用户的id，所以就不能添加成功

 

外键约束 FOREIGN KEY 维护

在更新、删除主表时，外键约束有可配置的触发事件，分别为：

1.RESTRICT（限制外表中的外键改动）
2.CASCADE（跟随外键改动）
3.SET NULL（设置为null）
4.SET DEFAULT（设默认值）
5.NO ACTION（同RESTRICT,限制外表中的外键改动）

添加触发事件的方法：
1.在创建表时：在创建外键时，直接追加 ON UPDATE 事件 ON DELETE 事件。
2.在存在表的情况下，

ALTER TABLE `info` ADD CONSTRAINT `fk_userId` FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON UPDATE CASCADE ON DELETE CASCADE;