• 中文
    • English
  • 注册
  • 查看作者
    • 3-3:MySQL 外键约束的参照操作

      一.  前言

      创建外键的时候,我们可以协商外键约束的参照操作,一共有四个选项:

      • CASCADE :从父表删除或更新且自动删除或更新子表中匹配的行

      • SET NULL: 从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL

      • RESTRICT: 拒绝对父表的删除或更新操作。

      • NO ACTION: 标准SQL的关键字,在MySQL中与RESTRICT相同

      那么这四个操作是指进行了外键约束的创建以后,在更新表的时候,子表是否也进行相应的操作,接下来我们以CASCADE为例讲解。

      二.  CASCADE

      CASCADE是指从父表删除或更新且自动删除或更新子表中匹配的行,首先我们再创建一个数据表,并设置外键约束的参照操作的为CASCADE:

      mysql> CREATE TABLE users1(
          -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
          -> username VARCHAR(10) NOT NULL,
          -> pid SMALLINT UNSIGNED,
          -> FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE
          -> );
      Query OK, 0 rows affected (0.01 sec)

      然后查看一下user1表的创建命令:

      mysql> SHOW CREATE TABLE users1;
      +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table  | Create Table                                                                                                                                                                                                                                                                                                                                |
      +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | users1 | CREATE TABLE `users1` (
        `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
        `username` varchar(10) NOT NULL,
        `pid` smallint(5) unsigned DEFAULT NULL,
        PRIMARY KEY (`id`),
        KEY `pid` (`pid`),
        CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
      +--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      可以看到ON DELETE CASCADE,指的就是从父表删除或更新且自动删除或更新子表中匹配的行,我们来验证一下:

      既然要更新相应的行,那么也就代表数据表中必须存在中相应的记录才可以,所以需要在2张表中插入记录,值得注意的是,必须现在父表中插入记录,然后再在子表中插入记录,首先在上一节中的省份表中插入记录并检验是否插入成功:

      mysql> INSERT provinces(pname) VALUES('A');
      Query OK, 1 row affected (0.01 sec)
      
      mysql> INSERT provinces(pname) VALUES('B');
      Query OK, 1 row affected (0.00 sec)
      
      mysql> INSERT provinces(pname) VALUES('C');
      Query OK, 1 row affected (0.00 sec)
      
      mysql> SELECT * FROM provinces;
      +----+-------+
      | id | pname |
      +----+-------+
      |  1 | A     |
      |  2 | B     |
      |  3 | C     |
      +----+-------+
      3 rows in set (0.00 sec)

      接下来再在users1表中插入记录:

      mysql> INSERT users1(username,pid) VALUES('Tom',3);
      Query OK, 1 row affected (0.00 sec)
      
      mysql> INSERT users1(username,pid) VALUES('John',7);
      ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE)
      mysql> INSERT users1(username,pid) VALUES('John',1);
      Query OK, 1 row affected (0.00 sec)
      
      mysql> INSERT users1(username,pid) VALUES('rose',3);
      Query OK, 1 row affected (0.00 sec)

      值得注意的是,当我们插入第二条记录的时候,因为pid没有7所以没有插入成功,但是编号也会自动递增1,所以我们查看一下users1记录,会发现id是1,3,4,而不是1,2,3

      mysql> SELECT * FROM users1;
      +----+----------+------+
      | id | username | pid  |
      +----+----------+------+
      |  1 | Tom      |    3 |
      |  3 | John     |    1 |
      |  4 | rose     |    3 |
      +----+----------+------+
      3 rows in set (0.00 sec)

      现在我们在省份表中删除id为3的记录:

      mysql> DELETE FROM provinces WHERE id = 3;
      Query OK, 1 row affected (0.01 sec)

      可以看到删除成功,一条被影响, 然后我们查看一下省份表:

      mysql> SELECT * FROM provinces;
      +----+-------+
      | id | pname |
      +----+-------+
      |  1 | A     |
      |  2 | B     |
      +----+-------+
      2 rows in set (0.00 sec)

      可以看到id为3的记录已经被删除,再查看一下users1表,发现所有pid为3的记录也已经被删除,CASCADE生效:

      mysql> SELECT * FROM users1;
      +----+----------+------+
      | id | username | pid  |
      +----+----------+------+
      |  3 | John     |    1 |
      +----+----------+------+
      1 row in set (0.00 sec)

      另外,在本节教程的最后关于实际开发中的注意事项,由Java家洼整理如下:

      在外键约束当中,在实际的开发过程中,我们很少使用物理的外键约束,很多都去使用逻辑的外键约束

      因为物理的外键约束只有INNODB这种引擎才会支持,像我们另外的一种引擎MYISAM的引擎则不支持

      反过来说,如果我想创建的数据表,假设存储引擎为MYISAM,而且又想使用外键约束的话,其实是不可能实现的

      所以说,我们在实际的项目开发中,我们不去定义物理的外键

      所谓的逻辑外键指的是就是我们在定义两张表的结构的时候,我们是按照存在的某种结构的方式去定义,但是不去使用FOREIGN KEY这个关键词来定义。

    • 0
    • 3
    • 0
    • 2.9k
    • 请登录之后再进行评论

      登录
    • 0
      张甲49站长
      @geniuspig 谢谢支持!
    • 0
      写得很好,希望更多人能看到。
    • 0
      打赏了449金币。
    • 赞助本站

      • 支付宝
      • 微信
      • QQ

      感谢一直支持本站的所有人!

      单栏布局 侧栏位置: