How to delete data from parent table in mysql5?

Hi I am using mysql5 innoDB database. I want to开发者_如何转开发 forcefully delete data from parent row and its related child table data's. How can I do that. Any one can help me.

---

If you use innodb you can use FOREIGN KEY CONSTRAINTS for cascading deletion when you remove parent row the children also will be removed.

See more http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

Another approach is to use Multiple-table syntax in delete. Look at http://dev.mysql.com/doc/refman/5.1/en/delete.html

Final approach is using trigger on after delete action. Look at http://dev.mysql.com/doc/refman/5.1/en/trigger-syntax.html

Also you can use sequential delete statements but in this case you should use transaction. Note: this one works only with innodb tables.

---

Two deletes in a row would do the trick:

delete from ChildTable where ParentID = ?
delete from ParentTable where ID = ?

---

If you are using Foreign Key constraint , then You can use on delete cascade Clause while creating a child table to remove a row from parent table, then it will remove the corresponding primary key row from child table also.

If you want to remove row only from parent table not the corresponding primary key row from the child table then use on delete cascade null Clause while creating child table.

For example : create a parent table called "student" , and Child table called "library"

create table student(sno integer(5) primary key,sname varchar(20));

insert into student values(1,"suman");
insert into student values(2,"sai"); 
insert into student values(3,"saaaa");

create table library(sno integer(5) references primary key(sno) on delete cascade,book_name varchar(20));

insert into student values(1,"c");
insert into student values(2,"JAVA");
insert into student values(3,"ORACLE");

Now delete second row from parent table

delete from student where sno=2;

then it will delete the corresponding row (2 JAVA) from child table. Because you are using the "on delete cascade" clause only.

sippose if you are using on delete cascade null then it will remove only the row (2 Sai) from parent table.

:) :):) :):) :):) :):) :)