Mysql:如果更新了父级状态，则更改所有子级记录的状态 [英] Mysql: Change status of all child records if status of parent is updated

问题描述

我有2张桌子

1.父母

Parent_ID(PK)| name    | status
--------------------------------
1            |parent 1 |enable
2            |parent 2 |enable
3            |parent 3 |disable

2.儿童

Child_Id(PK)| Parent_ID(Fk of parent table) | name     | status
----------------------------------------------------------
1           |1                              | child 1  | enable
2           |1                              | child 2  | enable
3           |1                              | child 3  | enable
4           |1                              | child 4  | enable
5           |2                              | child 5  | enable
6           |2                              | child 6  | enable
7           |2                              | child 7  | enable
8           |2                              | child 8  | enable
9           |3                              | child 9  | disable
10          |3                              | child 10 | disable
11          |3                              | child 11 | disable
12          |3                              | child 12 | disable

现在，我想在两个表之间设置一个关系，以便如果父表中记录的状态发生变化，那么其所有子行的状态也应发生变化.

Now I want to set a relation between both the tables such that if status of a record in parent tables changes then status of all its child row should also get changes.

我知道我可以使用触发器来做到这一点，但我认为应该有一些方法可以解决多列上的关系和FK约束.

I know I can do this with triggers but I think there should eb some way to do this with relations and FK constraint on multiple columns.

推荐答案

您需要在child表中创建引用parent_id和status的复合外键.

You need to create a composite foreign key in the child table referring to parent_id and status.

这是一个演示:

-- ----------------------------
-- Table structure for `parenttable`
-- ----------------------------
DROP TABLE IF EXISTS `parenttable`;
CREATE TABLE `parenttable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `ID` (`ID`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of parenttable
-- ----------------------------
INSERT INTO `parenttable` VALUES ('1', '1');
INSERT INTO `parenttable` VALUES ('2', '0');
INSERT INTO `parenttable` VALUES ('3', '1');

-- ----------------------------
-- Table structure for `childtable`
-- ----------------------------
DROP TABLE IF EXISTS `childtable`;
CREATE TABLE `childtable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `fk_childTable_parent_id` (`parent_id`,`status`),
  CONSTRAINT `fk_childTable_parent_id` FOREIGN KEY (`parent_id`, `status`) REFERENCES `parenttable` (`ID`, `status`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of childtable
-- ----------------------------
INSERT INTO `childtable` VALUES ('1', '1', '1');
INSERT INTO `childtable` VALUES ('3', '1', '1');
INSERT INTO `childtable` VALUES ('6', '1', '1');
INSERT INTO `childtable` VALUES ('4', '2', '0');
INSERT INTO `childtable` VALUES ('5', '2', '0');

测试:

现在尝试更新父表中ID = 1的status字段.

Now try to update the status field of ID = 1 in parent table.

此更改还将触发child表中所有子项的状态值的更改.

This change will trigger the change in the status values of all the child entries in the child table too.

UPDATE parentTable SET status = 0 WHERE ID = 1;

SELECT * FROM childTable WHERE parent_id = 1;

输出:

ID     parent_id     status

1         1            0
2         1            0
3         1            0

_{对于DELETE操作}

请参见演示

如果以后需要添加外键约束:

And if you need to add foreign key constraint later:

ALTER TABLE `childTable` ADD CONSTRAINT `fk_childTable_parent_id_status` FOREIGN KEY (
    `parent_id`,
    `status`
) REFERENCES `parentTable` (`ID`, `status`) ON DELETE CASCADE ON UPDATE CASCADE;

这篇关于Mysql:如果更新了父级状态，则更改所有子级记录的状态的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！