I have a database called music and have 4 tables inside;
I want to delete all information relating to cd005 (that's a cd_id column entry) in the database.
I guess I could use the
DELETE FROM table_name WHERE cd_id='cd005'
on every individual table, but I would like to know if there's a way to tackle this problem by deleting the data related to this id from the whole database at once.
Yes there is a way. Please check mysql documantation for foreign keys cascade deletes.
Example:
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 set id=1;
Insert into parent set id=2;
Insert into parent set id=3;
Insert into child set id=1, parent_id=1;
Insert into child set id=2, parent_id=1;
select * from parent;
select * from child;
delete from parent where id=1;
select * from parent;
select * from child;
ID
1
2
3
Record Count: 3; Execution Time: 0ms View Execution Plan
ID PARENT_ID
1 1
2 1
Record Count: 2; Execution Time: 0ms View Execution Plan
Record Count: 0; Execution Time: 1ms
ID
2
3
Record Count: 2; Execution Time: 0ms View Execution Plan
Record Count: 0; Execution Time: 0ms
