Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Delete related records with one query

Delete related records with one query

Author: rootsql Date: 7-11-2011, 22:26

Delete related records with one query (using ON DELETE CASCADE foreign key).

 

This is only possible for the InnoDb tables where foreign keys are supported.

CREATE TABLE dept(
  deptno INT(11) NOT NULL,
  dname VARCHAR(14) DEFAULT NULL,
  loc VARCHAR(13) DEFAULT NULL,
  PRIMARY KEY (deptno)
)
ENGINE = INNODB;
CREATE TABLE emp(
  empno INT(11) NOT NULL,
  ename VARCHAR(10) DEFAULT NULL,
  job VARCHAR(9) DEFAULT NULL,
  mgr INT(11) DEFAULT NULL,
  hiredate DATE DEFAULT NULL,
  sal DECIMAL(7, 2) DEFAULT NULL,
  comm DECIMAL(7, 2) DEFAULT NULL,
  deptno INT(11) DEFAULT NULL,
  PRIMARY KEY (empno),
  CONSTRAINT FK_emp_dept_deptno FOREIGN KEY (deptno)
  REFERENCES dept (deptno) ON DELETE CASCADE
)
ENGINE = INNODB;

 

This command will delete a record in the `dept` table and all related records (with deptno = 1) in the child table `emp`:

DELETE FROM dept WHERE deptno = 1;
Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
Information
Members of Guests cannot leave comments.