不设置引用选项时 默认为restrict效果同no action

1，先删除父表的话 会报错 2，只能先删除子表中的记录

  2. mysql> create table parent(id int not null primary key);
  3. Query OK, 0 rows affected (0.04 sec)
  5. mysql> create table child(id int,parent_id int,foreign key(parent_id) references parent(id));
  6. Query OK, 0 rows affected (0.04 sec)
  8. mysql> insert into parent select 1;
  9. Query OK, 1 row affected (0.01 sec)
  10. Records: 1  Duplicates: 0  Warnings: 0
  12. mysql> insert into parent select 2;
  13. Query OK, 1 row affected (0.01 sec)
  14. Records: 1  Duplicates: 0  Warnings: 0
  16. mysql> select * from parent;
  17. +----+
  18. | id |
  19. +----+
  20. |  1 |
  21. |  2 |
  22. +----+
  23. 2 rows in set (0.00 sec)
  25. mysql> insert into child select 1,1;
  26. Query OK, 1 row affected (0.01 sec)
  27. Records: 1  Duplicates: 0  Warnings: 0
  29. mysql> insert into child select 2,2;
  30. Query OK, 1 row affected (0.01 sec)
  31. Records: 1  Duplicates: 0  Warnings: 0
  33. mysql> select * from child;
  34. +------+-----------+
  35. | id   | parent_id |
  36. +------+-----------+
  37. |    1 |         1 |
  38. |    2 |         2 |
  39. +------+-----------+
  40. 2 rows in set (0.00 sec)
  42. mysql> delete from parent where id = 1;
  43. ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`blog`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
  44. mysql>
  45. mysql>
  46. mysql> delete from child where id = 1;
  47. Query OK, 1 row affected (0.01 sec)
  49. mysql> delete from parent where id = 1;
  50. Query OK, 1 row affected (0.01 sec)
  52. mysql> drop table parent;
  53. ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
  54. mysql> drop table child;
  55. Query OK, 0 rows affected (0.02 sec)
  57. mysql> drop table parent;
  58. Query OK, 0 rows affected (0.02 sec)

级联操作 设置引用选项为set null




mysql> create table parent(id int primary key);
Query OK, 0 rows affected (0.04 sec)

mysql> create table child(id int,parent_id int,foreign key(parent_id) references parent(id) on delete set null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into parent select 1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into parent select 2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into child select 1,1;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into child select 2,2;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> delete from parent where id = 1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from child;
+------+-----------+
| id   | parent_id |
+------+-----------+
|    1 |      NULL |
|    2 |         2 |
+------+-----------+
2 rows in set (0.00 sec)