mysql - 外键关系约束 - 级联更新也外键 id 的其他值

Question

初始状态

parents
id    parent_validity
1     1
2     1

children
id    parent_id    child_validity
1     1            1
2     1            1
3     2            1

更新后所需的状态

parents
id    parent_validity
1     0 (updated as 0)
2     1

children
id    parent_id    child_validity
1     1            0 (This val is expected as 0)
2     1            0 (This val is expected as 0)
3     2            1 (This val is expected as no change)

我得到了什么

children
id    parent_id    child_validity
1     1            0
2     1            0
3     2            0 (all values under this column became 0)

我想要的是，如果我更新表parents中的parent_validity，然后更新表children中的相关child_validity应该更新。我所说的“相关”指的是id的外键关系。

我的结果是:表parents中的任何有效性更改都会更改表children中的所有有效性。

我不想用 PHP 做我需要做的事情。让 MySQL 处理。

我无法在网络中获得符合我的请求的正确搜索词，因此无法找到任何解决方案。你能帮我一下吗？

感谢您的帮助。

注意:我认为我的 q 标题需要更新。 (这是我最好的，抱歉。)

我的sql来创建并给出约束

CREATE TABLE `parents` (
  `id` int(2) unsigned NOT NULL AUTO_INCREMENT,
  `parent_validity` bit(1) NOT NULL COMMENT '1:valid or 0:invalid',  
  PRIMARY KEY (`id`),
  KEY (`parent_validity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `children` (
  `id` int(2) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(2) unsigned NOT NULL,
  `child_validity` bit(1) NOT NULL COMMENT '1:valid or 0:invalid',  
  PRIMARY KEY (`id`),
  KEY (`child_validity`),
  CONSTRAINT fk_parent_id FOREIGN KEY (parent_id)  REFERENCES parents(id) ON UPDATE CASCADE,
  CONSTRAINT fk_parent_validity FOREIGN KEY (child_validity)  REFERENCES parents(parent_validity) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

选项是复合键，但您应该评估这对您的解决方案带来的设计影响:

mysql> DROP TABLE IF EXISTS `children`, `parents`; 
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `parents` (
    ->   `id` int(2) unsigned NOT NULL AUTO_INCREMENT,
    ->   `parent_validity` bit(1) NOT NULL COMMENT '1:valid or 0:invalid',  
    ->   PRIMARY KEY (`id`, `parent_validity`)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `children` (
    ->   `id` int(2) unsigned NOT NULL AUTO_INCREMENT,
    ->   `parent_id` int(2) unsigned NOT NULL,
    ->   `child_validity` bit(1) NOT NULL COMMENT '1:valid or 0:invalid',  
    ->   PRIMARY KEY (`id`),
    ->   KEY (`parent_id`, `child_validity`),
    ->   CONSTRAINT `fk_parent_id_child_validity` FOREIGN KEY (`parent_id`, `child_validity`)
    ->   REFERENCES `parents` (`id`, `parent_validity`) ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO
    ->   `parents`
    -> VALUES
    ->   (NULL, 1), (NULL, 1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO
    ->   `children`
    -> VALUES
    ->   (NULL, 1, 1),
    ->   (NULL, 1, 1),
    ->   (NULL, 2, 1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `id`,
    ->   CONVERT(`parent_validity`, UNSIGNED) `parent_validity`
    -> FROM
    ->   `parents`;
+----+-----------------+
| id | parent_validity |
+----+-----------------+
|  1 |               1 |
|  2 |               1 |
+----+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT
    ->   `id`,
    ->   `parent_id`,
    ->   CONVERT(`child_validity`, UNSIGNED) `child_validity`
    -> FROM
    ->   `children`;
+----+-----------+----------------+
| id | parent_id | child_validity |
+----+-----------+----------------+
|  1 |         1 |              1 |
|  2 |         1 |              1 |
|  3 |         2 |              1 |
+----+-----------+----------------+
3 rows in set (0.00 sec)

mysql> UPDATE `parents`
    -> SET `parent_validity` = 0
    -> WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT
    ->   `id`,
    ->   CONVERT(`parent_validity`, UNSIGNED) `parent_validity`
    -> FROM
    ->   `parents`;
+----+-----------------+
| id | parent_validity |
+----+-----------------+
|  1 |               0 |
|  2 |               1 |
+----+-----------------+
2 rows in set (0.00 sec)

mysql> SELECT
    ->   `id`,
    ->   `parent_id`,
    ->   CONVERT(`child_validity`, UNSIGNED) `child_validity`
    -> FROM
    ->   `children`;
+----+-----------+----------------+
| id | parent_id | child_validity |
+----+-----------+----------------+
|  1 |         1 |              0 |
|  2 |         1 |              0 |
|  3 |         2 |              1 |
+----+-----------+----------------+
3 rows in set (0.00 sec)