MySql多对多关系中外键的应用_MySQL

Mysql外键

业务需求：用户表r_user保存用户名等信息。现需要给每个用户设置工作基地，一个用户可以有多个工作基地，多个用户也可以有一个工作基地，即多对多关系。（外键，若有两个表a，b，c是a的主键，而b中也有c字段，则c就是表b的外键，外键约束主要用来维护两个表之间数据的一致性）

设计方案：

方案一：建立一张用户基地表，与r_user与用户基地表，保持一对多的关系，如图所示，r_user的主键id做为r_user_base的外键user_id。通过r_user中的id，在r_user_base表中load该用户的所有工作基地。

ShopEx 网上商店系统

国产著名网上商店系统，真正企业级应用软件，性能卓越，在国内外享有盛誉，用户遍布欧洲、美洲、大洋洲，支持多语言，前台与后台均可设置为不同语言界面，用户帮助文档极其丰富，PHP+MySQL+Zend运行环境，让你快速建立个性化的网上商店，内置几十种网上支付网关、内置数十套精美模板，支持实体、非实体商品销售。 更新功能调整： 1、应用中心：APP的“更新时间”字段

0

查看详情

方案二：建立一张个基地表base_info保存目前存在的所有基地，再建一张user_base关系表。如图所示，关系表user_base有两个外键user_id与base_id 

方案一的特点是，只需要键一张表就可以完成业务需求。缺点是不够模块化，如果在其它地方还要用到基地信息，则还要再建基地表

方案二的特点是，用一张关系表连接两张信息表。便于信息表的维护与重复利用。

基于业务需求与以后扩展及重用性考虑，采用方案二实现需求。

在MySQL 3.23.44版本后，InnoDB引擎类型的表支持了外键约束。

外键的使用条件：

1.两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）；

2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立； 

3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

外键的定义语法：

[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}]

 cascade方式 

   在父表上update/delete记录时，同步update/delete掉子表的匹配记录 
   On delete cascade从mysql3.23.50开始可用; on update cascade从mysql4.0.8开始可用 。

 set null方式 

   在父表上update/delete记录时，将子表上匹配记录的列设为null 

   要注意子表的外键列不能为not null 

   On delete set null从mysql3.23.50开始可用; on update set null从mysql4.0.8开始可用 
No action方式 

 如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作 
   这个是ANSI SQL-92标准,从mysql4.0.8开始支持 
Restrict方式 

  同no action, 都是立即检查外键约束 

建立人员信息表：

1 CREATE TABLE `r_user` (2   `id` bigint(20) NOT NULL AUTO_INCREMENT,3   `NAME` varchar(20) DEFAULT NULL,4   `PASSWORD` varchar(50) DEFAULT NULL,5   `STAFF_NUM` varchar(20) DEFAULT NULL,6    `USER_NAME` varchar(20) DEFAULT NULL,7   PRIMARY KEY (`id`),8   ) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8 

建立基地信息表

1 CREATE TABLE `branch_info` (2   `ID` bigint(20) NOT NULL AUTO_INCREMENT,3    `BRANCH_CODE` varchar(255) DEFAULT NULL,4   `BRANCH_DESC` varchar(255) DEFAULT NULL,5   PRIMARY KEY (`ID`)6 ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 

关系表：

1 CREATE TABLE `user_work_base` (2   `id` bigint(20) NOT NULL AUTO_INCREMENT,3   `version` int(11) NOT NULL,4   `user_id` bigint(20) NOT NULL ,5   `base_id` bigint(20) NOT NULL ,6   PRIMARY KEY (`id`),7   CONSTRAINT `user_work_base_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `r_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,8   CONSTRAINT `user_work_base_ibfk_2` FOREIGN KEY (`base_id`) REFERENCES `branch_info` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE9 ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8

删除人员r_user表中人员A，关系表user_base自动删除A的关系数据。

如果外键使用Restrict方式，只删除A，则报错。

Cannot delete or update a parent row: a foreign key constraint fails (`maircrew`,`user_work_base`, CONSTRAINT `FK41EB46D32AA89EA0` 

FOREIGN KEY (`user_id`) REFERENCES `r_user` (`id`))