I am working on purging a huge database (~ 1TB data ) with over 10 million records using InnoDB engine. Whoever set up the database schema, set the foreign keys but did not set on delete cascade. I want to do a selective time based purge. Two parts to my question
10 million records is tiny.
1TB is huge.
This points to the use of humongous BLOBs. Always a good idea to store huge BLOBs in the database: everyone knows the longer backups take, the better.
Now,
Can I set "on delete cascade" without any side-effects ?
Yeah, as long as you don't DELETE anything...
Can I simulate my delete to check for foreign key reference violation ? I know I can use START TRANSACTION and ROLLBACK TRANSACTION, but it is not something I am looking for
Yeah, but the ROLLBACK will probably take just as long as the operation you cancelled, if not more, and the best thing is... you can't interrupt it! You will sit in front of your terminal... and you have no idea when it will end!
Now, either it is "ON DELETE CASCADE" and it won't fail, it'll just keep deleting for a few days if you screw it up, or it's ON DELETE RESTRICT and it will fail on the first row it attempts to delete, and there will be nothing to roll back, so it will be fast. Both case don't "simulate" anything useful though.
Or it's ON DELETE SET NULL, and your database ends up screwed up.
Now, if you have huge BLOBs, and you want to mess around in your database, this is what I would suggest: make a copy of the database, but without the BLOBs. You know, CREATE TABLE LIKE... and then INSERT INTO SELECT. Just remove the huge BLOB column. Then you can mess around as you like and practice, check if your queries actually delete the right thing...
Note: if you delete like 90% of your database, consider creating new tables (with INSERT INTO SELECT only the rows you wanna keep) instead of DELETE. Because DELETE doesn't shrink the files.
About that: think about it first. If you copy what you want to keep in a new database, read the docs (I don't remember the details) but perhaps you need to make a tablespace or something, so later you can purge your old 1TB almost-empty ibdata file.