CODEWITHSUNDEEP
mysqldatabasesql-delete
Alex Encore
Alex Encore

Reputation: 309

Delete all information of an entry from the database (MySQL)

I have a database called music and have 4 tables inside;

  1. band_members
  2. bands
  3. cds
  4. releases.

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.

Upvotes: 0

Views: 205

Answers (1)

Nesim Razon
Nesim Razon

Reputation: 9794

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

Upvotes: 1

Related Questions