Script to Delete Data from Mysql Tables with Foreign Key Constraints

Question

I have list of tables (Mysql) where some of them have reference.

I want to to generate delete script for all the tables based on time column.

NOTE: we can not user delete Cascade I can not disable the keys as it is production system.

Your question is unanswerable given the massive lack of information. Please include table structure and data. — Tim Biegeleisen
– Tim Biegeleisen, Commented Mar 20, 2018 at 9:42
U can think any table structure as it do not have to do any thing with data. — sandeep rawat
– sandeep rawat, Commented Mar 20, 2018 at 10:03
Foreign key constraints keep you from deleting one or more rows in a table. Apparently you don't want to delete the rows with foreign keys because you don't want to use delete cascade. Also, you don't want to orphan those rows because you don't want to turn off foreign key checks. Just what do you want to do with those rows with foreign keys? That seems more of a business question than a technical question. — Honeyboy Wilson
– Honeyboy Wilson, Commented Mar 20, 2018 at 13:51
yes you are right but it should be common issue if we have to delete data in prod .. — sandeep rawat
– sandeep rawat, Commented Mar 21, 2018 at 2:37

quentino · Accepted Answer · 2018-03-20 09:42:14Z

3

You can temporarily disable check reference

SET foreign_key_checks = 0;

answered Mar 20, 2018 at 9:42

quentino

1,2211 gold badge12 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

thanks for response but we can not disable key in prod.