MySQL外键级联

外键介绍

在数据库中的某一张表中， 通过外键字段，可以把数据与另一张表管理起来.

我们举个例子来理解外键:

首先创建两张表，分别为Customers和Orders， Customers为顾客信息表, 而Orders作为订单表:

创建Customers表


        
CREATE TABLE `Customers` (
  `cust_id` char(10) NOT NULL,
  `cust_name` char(50) NOT NULL,
  `cust_address` char(50) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      

    
  






创建Orders表



  

    
      

        

        
CREATE TABLE `Orders` (
  `order_id` int(11) NOT NULL,
  `order_date` datetime NOT NULL,
  `cust_id` char(10) NOT NULL,
  KEY `cust_id` (`cust_id`),
  CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`cust_id`) REFERENCES `Customers` (`cust_id`) ON DELETE CASCADE ON UPDATE
  CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

      

    
  






其中cust_id字段作为外键联结Orders和Customers表, 外键绑定关系这里使用了ON DELETE CASCADE, ON UPDATE CASCADE, 即如果外键对应数据被删除或者更新时，将关联数据完全删除或者相应地更新.使用了插入数据后的两个表分别如下:


Customers顾客信息表



  
      

          cust_id
          cust_name
          cust_address
      

  
  
      

          1000000001
          Tom
          200 Maple Lane
      

      

          1000000002
          Jim
          1 Sunny Place
      

      

          1000000003
          John
          829 Riverside Drive
      

      

          1000000004
          Smith
          4545 53rd Street
      

  



Orders订单表



  
      

          order_id
          order_data
          cust_id
      

  
  
      

          20005
          2019-02-03 00:00:00
          1000000001
      

      

          20006
          2019-02-07 00:00:00
          1000000002
      

      

          20007
          2019-03-06 00:00:00
          1000000003
      

  



外键约束


    

  1. 

    两个表必须是InnoDB表，MyISAM表暂时不支持外键.
    

    2. 

  2. 

    外键列必须建立了索引， 目前MySQL5.6及以上的版本支持创建外键时自动创建索引， 所依无需显式的创建索引.
    

    3. 

  3. 

    被外键引用的列必须是主键或唯一索引.
    

    4. 

  4. 

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

    5. 



根据约束， 假如我们插入Orders表一条数据，但其中的cust_id字段是Customers不存在的， MySQL会提示如下错误:



  

    
      

        

        
mysql> insert into Orders values(20006, '2019-09-03 00:00:00', '1000000006');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foreign_key`.`Orders`, CONSTRAINT `Orders_ibfk_1` FOREIGN KEY (`cust_id`) REFERENCES `Customers` (`cust_id`))

      

    
  






外键级联


修改外键数据


我们将Customers表中的第一列数据进行修改：



  

    
      

        

        
mysql> UPDATE Customers SET cust_id=1000000005 WHERE cust_name='Tom';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

      

    
  






根据ON UPDATE CASCADE,Orders表中的对应的数据也被修改:



  

    
      

        

        
mysql> SELECT order_num, order_date, cust_id FROM Orders;
+-----------+---------------------+------------+
| order_num | order_date          | cust_id    |
+-----------+---------------------+------------+
|     20005 | 2019-02-03 00:00:00 | 1000000005 |
|     20006 | 2019-02-07 00:00:00 | 1000000002 |
|     20007 | 2019-03-06 00:00:00 | 1000000003 |
+-----------+---------------------+------------+
3 rows in set (0.00 sec)