3

Is there any equivalence to SQL Server: ALTER TABLE mytable NOCHECK CONTRAINT my_fk_1 in MySQL?

I know I can turn off all constraints in a table using "SET foreign_key_checks = 0;", but I want to disable only one constraint, not all.

I guess there is no way to achieve this in the current version of MysQL, but I wanted to ask to see if someone knows a workaround to this limitation.

TIA.

Community
  • 1
  • 1
lepe
  • 24,677
  • 9
  • 99
  • 108
  • One alternative could be to DROP the constraint, import the data, add the constraint without checking and then enable it... – lepe Aug 18 '11 at 03:32
  • 1
    lepe's idea is what I would do too, but note that you can get bad data in the FK column. When you recreate the FK, mysql will **not** complain about the bad references, leaving you with "corrupted" data. – Bohemian Aug 18 '11 at 03:47
  • 3
    @Bohemian there is a clever/complete/efficient source code MySQL script that defines and uses a stored procedure to check all foreign key constraints in all databases and tables specified by "LIKE" style matches, posted at http://stackoverflow.com/questions/2250775/force-innodb-to-recheck-foreign-keys-on-a-table-tables/5977191#5977191 Just the thing to have in hand to run after importing data with constraints disabled. A variation of it is posted that generates SQL source to delete rows containing bad keys, as you would get if the original database had cascaded deletes. – sootsnoot Jan 16 '13 at 07:51

0 Answers0