I want to delete specific rows from 8 tables. My problem is that the rows are connected with foreign key. How can I delete all the data that connected to the specific rows that I want to delete? My tables include definition tables (like id, name ,max value, min value...), data tables (like id, user_id, definition_id,....) and history tables (save every change in data table).
I thought to use delete on cascade command but I could not find a way to use it.
DELETE CASCADE is an attribute of the foreign key constraint. Unfortunately it's not something you can use as an option with a DELETE statement (which would be really cool actually)
If your foreign keys have not been declared as cascading you need to "work your way up".
Unfortunately you did not show us your real table structure so let's assume something like this:
main_table (main_id)
child_one (id, main_id)
child_two (id, id_one)
child_three (id, id_two)
(I know you said 8 tables, but for the sake of the demonstration I shortened it a bit, but that doesn't change the underlying "strategy")
Assuming you want to delete the row with main_id = 42 from `main_table:
You first need to delete the rows from child_three using something like this:
delete from child_three
where id_two in (select id
from child_two
where id_one in (select id
from child_one
where main_id = 42);
Then delete the rows from child_two:
delete from child_two
where id_one in (select id
from child_one
where main_id = 42);
Then child_one:
delete from child_one
where main_id = 42;
And finally the main table:
delete from main_table
where id = 42;
Some SQL clients can actually generate those statements for you. I don't know if SQL Developer can though.
I assume that you use InnoDB Engine, since you are talking about foreign keys,
Easier will be to define properly the table so that a deletion will act as a cascade deletion.
CONSTRAINT `myForeignKey` FOREIGN KEY (`typeId`)
REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
Here is alink with a proper create table statement:
