Arpita Arpita - 2 months ago 7
MySQL Question

Mysql: Change status of all child records if status of parent is updated

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

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;
Comments