Mysql: Change status of all child records if statu

Question

I have 2 tables

1. Parent

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

2. Child

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.

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.

Answer 1

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

Here's a demo:

-- ----------------------------
-- 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');

Test:

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

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;

Output:

ID     parent_id     status

1         1            0
2         1            0
3         1            0

_{Same holds for DELETE operation}

See Demo

---

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;