数据库的外键约束，可以让数据进行一致性更新，但是会有一定的性能损耗，线上业务使用不多。通常上述级联更新和删除都是由应用层业务逻辑进行判断并实现。

    1. --
    2. -- 表结构 摘自  MySQL 官方文档
    3. --
    5. mysql> create table parent (
    6.     ->     id int not null,
    7.     ->     primary key (id)
    8.     -> ) engine=innodb;
    9. Query OK, 0 rows affected (0.14 sec)
    11. mysql> create table child (
    12.     ->     id int, 
    13.     ->     parent_id INT,
    14.     ->     index par_ind (parent_id),
    15.     ->     foreign key (parent_id) 
    16.     ->         references parent(id)
    17.     ->         on delete cascade on update cascade  -- 比官网例子增加 update cascade 
    18.     -> ) engine=innodb;
    19. Query ok, 0 rows affected (0.15 sec)
    21. mysql> insert into child values(1,1);  -- 我们插入一条数据，id=1，parent_id=1
    22. ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE)  
    23. -- 直接报错了，因为此时parent表中没有任何记录
    25. mysql> insert into parent values(1);   -- 现在parent中插入记录
    26. Query OK, 1 row affected (0.03 sec)
    28. mysql> insert into child values(1,1);  -- 然后在child中插入记录，且parent_id是在parent中存在的
    29. Query OK, 1 row affected (0.02 sec)
    31. mysql> insert into child values(1,2);  -- 插入parent_id=2的记录，报错。因为此时parent_id=2的记录不存在
    32. ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE)
    34. mysql> select * from  child;     
    35. +------+-----------+
    36. | id   | parent_id |
    37. +------+-----------+
    38. |    1 |         1 |  -- parent_id = 1
    39. +------+-----------+
    40. 1 row in set (0.00 sec)
    42. mysql> select * from  parent;
    43. +----+
    44. | id |
    45. +----+
    46. |  1 |  -- 根据表结构的定义（Foreign_key），这个值就是 child表中的id
    47. +----+
    48. 1 row in set (0.00 sec)
    50. mysql> update parent set id=100 where id=1;       
    51. Query OK, 1 row affected (0.04 sec)
    52. Rows matched: 1  Changed: 1  Warnings: 0
    54. mysql> select * from parent;
    55. +-----+
    56. | id  |
    57. +-----+
    58. | 100 |  -- 已经设置成了100
    59. +-----+
    60. 1 row in set (0.00 sec)
    62. mysql> select * from child;
    63. +------+-----------+
    64. | id   | parent_id |
    65. +------+-----------+
    66. |    1 |       100 |  -- 自动变化，这是on update cascade的作用，联级更新，parent更新，child也跟着更新
    67. +------+-----------+
    68. 1 row in set (0.00 sec)
    70. mysql> delete from parent where id=100;  -- 删除这条记录
    71. Query OK, 1 row affected (0.03 sec)
    73. mysql> select * from parent;  -- id=100的记录已经被删除了
    74. Empty set (0.00 sec)
    76. mysql> select * from child;  -- id=1，parent_id=100的记录跟着被删除了。on delete cascade的作用
    77. Empty set (0.00 sec)
    79. mysql> alter table child drop foreign key child_ibfk_1;  -- 删除 之前的外键
    80. Query OK, 0 rows affected (0.07 sec)
    81. Records: 0  Duplicates: 0  Warnings: 0
    83. mysql> alter table child add foreign key(parent_id) 
    84.     -> references parent(id) on update cascade on delete restrict;  -- 使用严格模式
    85. Query OK, 0 rows affected (0.27 sec)
    86. Records: 0  Duplicates: 0  Warnings: 0
    88. mysql> insert into parent values(50);
    89. Query OK, 1 row affected (0.03 sec)
    91. mysql> insert into child values(3,50); 
    92. Query OK, 1 row affected (0.03 sec)
    94. mysql> insert into child values(3,51);  -- 和之前一样会提示错误
    95. ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE)
    97. mysql> delete from parent where id=50;  -- 删除失败了，因为是restrict模式
    98. ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`burn_test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE)
    100. -- 注意，delete 后面说明都不写表示 no action == restrict

    作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。