CODEWITHSUNDEEP
mysql
Aditya Kumar
Aditya Kumar

Reputation: 793

MYSQL: Delete a record from all the tables in a single shot

i am having a mysql database, and in one table i have f_id as P_Key and this f_id is referenced in multiple table as f_key. and i want to delete the records of this f_id(let say 10) from all the table in a once. then for this i need to delete in all the table which are having f_id= 10 as f_key then only i can delete this from the main table which contains it as a P_Key. can there be any automated process which can delete this by automatic looking into the table ,I dont want to write down the whole list of the table(as the list is very big more than 18 tables) in the query as this p_key is referenced in many tables.

Upvotes: 0

Views: 693

Answers (2)

Kibet Yegon
Kibet Yegon

Reputation: 2823

The easiest way to do this is set up an ON DELETE CASCADE CONSTRAINT on your keys such that on deletion of records on your parent table will delete all child records in your other tables that reference that row. Note that you'll need to delete the foreign keys and and add them again if they don't have an ON DELETE CASCADE constraint. Something like this:

SHOW CREATE TABLE [table_name]

ALTER TABLE [table_name] DROP FOREIGN KEY [f_id];

ALTER TABLE [table_name]
ADD CONSTRAINT [f_id]
FOREIGN KEY (`[f_id]`) REFERENCES `[other_table]` (`[column_name]`)
ON DELETE CASCADE;

Upvotes: 1

Mike Campbell
Mike Campbell

Reputation: 7978

You ought to be able to look into the information_schema tables to find out the names of your tables. Something like:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = "database_name"

replace database_name with the name of the database you want to inspect, and it should return all the table names. You might be able to do something with that list.

Upvotes: 0

Related Questions