3

I have a table with lots of data. PK from that table is referenced in 7 other tables (each one of them also holds a lot of data). Now, since there was never any FK between main table and other 7 tables, i have to find a way to remove all rows from main table that's not referenced by any other row in any of those 7 other tables. This is what i'm thinking of (all solutions should be implemented using stored procedures):

0) "delete from main table where id not in (select .. table 1) and not in (select..table2)...table 7" is out of the question, it would take forever to complete.

1) create a stored procedure that will make distinct, comma separated, set of used ids in all 7 tables and remove from the main table those ids not present in any set.

2) Make a "restrict" FK in every of those 7 tables and perform "Delete from main_table".

Solution No2 sounds best to me but i have a problem - i don't know how to tell mysql "delete from main table BUT skip rows which throw FK exception" :\ "Delete ignore" can't be used because it will stop removal as soon as it bumps upon row that throws FK error.

So, i have to ask you: is there a way for me to create "restrict" FK on each of those 7 tables and run "delete from main_table" with some option that will simply skip over any row which throws "FK constraint" error?

guest86
  • 2,894
  • 8
  • 49
  • 72

1 Answers1

3

You could use some of the resources available here:

How to temporarily disable a foreign key constraint in MySQL?

http://gauravsohoni.wordpress.com/2009/03/09/mysql-disable-foreign-key-checks-or-constraints/

In brief, you can disable a foreign key constraint or drop it:

ALTER TABLE table1 DROP FOREIGN KEY fk_name1; //get rid of current constraints ALTER TABLE table2 DROP FOREIGN KEY fk_name2;

OR

SET FOREIGN_KEY_CHECKS=0; //before your delete

SET FOREIGN_KEY_CHECKS=1; //after your delete

Community
  • 1
  • 1
Neels
  • 2,547
  • 6
  • 33
  • 40
  • I checked those links. Now i'm a bit confused :) I think i NEED FK, why should i disable it's check? My idea is to use FK check in order to know which row is being referenced and which isn't. The problem is that i need to keep some (unknown) amount of data in the "main_table". I don't have problems with foreign keys because currently i don't have any :) – guest86 Sep 24 '14 at 11:27