Salman Quader Salman Quader - 7 months ago 16
PHP Question

how delete parent row with all child row from other table

parent_table
----------------------------------------------------------------------
id name
1 a
2 b

child_table
---------------------------------------------------------------------
id parent_table_id name
1 1 c
2 1 d
3 1 e
4 2 f
5 2 g


When I will delete first row from parent table then all child row of parent first row.
how is it possible??

Answer

Use foreign keys:

CREATE TABLE parent (
  id INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
  id INT,
  parent_id INT,
  INDEX par_ind (parent_id),
  FOREIGN KEY (parent_id)
  REFERENCES parent(id)
  ON DELETE CASCADE
) ENGINE=INNODB;


INSERT INTO parent VALUES (1), (2);
INSERT INTO child VALUES (1, 1);

# This query implicitly removes from `child` where parent_id = 1
DELETE FROM parent WHERE id = 1;