MySQL - 外键

一、定义

1. 外键约束(FOREIGN KEY Constraint) ，用来维护从表（Child Table）和主表（Parent Table）之间的引用完整性.
2. 外键约束是个有争议性的约束，它一方面能够维护数据库的数据一致性，数据的完整性，防止错误的垃圾数据入库；
3. 另外一方面它会增加表插入、更新等SQL性能的额外开销，不少系统里面通过业务逻辑控制来取消外键约束

二 、语法格式

1、添加约束

1. 行级添加

   CREATE TABLE 表名
   (
       列名 数据类型 REFERENCES 参照表(参照列名)
      [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
      [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
       ...
   )
   --参照列名一般是主表的主键
   

2. 表级添加

   CREATE TABLE 表名
   (
       列名 数据类型 ,
       ...
       CONSTRAINT 约束名称(FK_TABLE_NAME) FOREIGN KEY(约束列)  REFERENCES 参照表(参照列名)
       [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
      [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
   )
   -- 外键名一般命名为《FK_表名_参照列名》
   

3. 创建表之后添加

   ALTER TABLE 表名
       ADD CONSTRAINT `约束名` FOREIGN KEY ('约束列')
       REFERENCES 参照表 ('列名')
       [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
      [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
   

2、删除约束

1. 语法格式

   ALTER TABLE 表名 DROP FOREIGN KEY 外键名;
   

三、示例代码

1. 主表

   --  主表
   CREATE  TABLE TB_USER(
       UID  INT(10) PRIMARY KEY AUTO_INCREMENT,
       USERNAME VARCHAR(64)
   );
   

2. 行级添加

   -- 子表
   CREATE  TABLE TB_ADDRESS(
       ADDRESS_ID int(10),
       USERNAME VARCHAR(64),
       UID int(10) REFERENCES TB_USER(UID)
   );
   

3. 表级添加

   -- 在子表上
   CREATE  TABLE TB_ADDRESS(
       ADDRESS_ID int(10),
       USERNAME VARCHAR(64),
       UID int(10) ,
       CONSTRAINT FK_TBUSER_UID FOREIGN KEY(UID) REFERENCES TB_USER(UID) ON DELETE SET NULL
   );
   

4. 创建表之后添加

    -- 创建子表
   CREATE  TABLE TB_ADDRESS(
       ADDRESS_ID int(10),
       USERNAME VARCHAR(64),
       UID int(10) REFERENCES TB_USER(UID) ,
   );
   --  主表子表创建完成之后单独添加
   ALTER TABLE TB_ADDRESS
       ADD CONSTRAINT `fk_user_uid` FOREIGN KEY (uid)
           REFERENCES t_user (uid) ON DELETE NO ACTION ON UPDATE NO ACTION;
   

5. 删除外键

   ALTER TABLE TB_ADDRESS DROP FOREIGN KEY 'fk_user_uid';
   

四、ON DELETE 与ON UPDATE

1、说明

表示删除或者更新主表的数据、子表如何操作

2、可选值

1. RESTRICT 与 No Action(sql标准):
   RESTRICT(默认值)，当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除
2. CASCADE:
   当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则也删除外键在子表（即包含外键的表）中的记录
3. SET NULL:
   当在父表（即外键的来源表）中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（不过这就要求该外键允许取null） ，支持ON DELETE SET NULL和ON UPDATE
4. SET DEFAULT:
   InnoDB目前不支持。

五、对DML与DDL的影响

1、INSERT

只有操作是在子表或从表这一端时才会产生违反引用完整性约束的问题，父表则不受影响。

2、DELETE

只有操作是在父表或主表这一端时才会产生违反引用完整性约束的问题，子表则会根据相关的可选值做响应的操作。

3、UPDATE

子表父表直接操作都会违反引用完整性约束。两种解决方法：

• 先更新子表的引用列为空，再更新父表的主键的列的值，然后把子表的引用列更新成新的父表的值；

• 使用ON DELETE SET NULL，先更新父表，然后将子表外键为空的记录更新为新的值。

4、DDL语句

DROP TABLE与TRUNCATE TABLE，操作父表，违反引用完整性约束，子表则不然

• 一般两个表如果有关联关系，就会把主表（一）的主键作为从表（多）的外键

• 创建从表之前必须保证主表存在，否则先创建主表。

• 删除表的时候先删除从表才能删除主表

六、总结

1. 两个表引擎必须为InnoDB，MyISAM不支持
2. 外键参照必须建立索引（必须是主键、唯一），MySQL如果没有会自动创建,SQL标准中必须是唯一,但Mysql对他进行了扩展,不必是唯一(实战开发中一般使用父表的主键作为子表的外键),
3. 在子表上创建外键约束
4. 外键关系的两个表的列必须是数据类型相似(建议保持相同)，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；
5. 外键的组合列不能超过32列
6. 子表和父表必须在同一个数据库。分布式数据库中，外键不能跨节点，但触发器可以你不能在CREATE TABLE语句中包含AS子查询子句定义一个外键约束。相反，你必须创建一个没有约束的表，然后添加ALTER TABLE语句