vtiger migration scripts. Adding FOREIGN KEY constraints often fails
I have mentioned this before but it appears to have gone unnoticed.
There are several instances during the migration where the scripts attempt to add FOREIGN KEY constraints to assist when deleting entries so the delete cascades across related tables.
Unfortunately, with many of my customers older vtiger systems the tables which have a relationship often do not have their keys "in sync". In my customised migration scripts I use them from the command line and set dieOnError(true). This results in me often getting failures such as this:
Query Failed:ALTER TABLE vtiger_seattachmentsrel ADD CONSTRAINT fk_2_vtiger_seattachmentsrel FOREIGN KEY (crmid) REFERENCES vtiger_crmentity(crmid) ON DELETE CASCADE::ADODB error Query Failed:ALTER TABLE vtiger_seattachmentsrel ADD CONSTRAINT fk_2_vtiger_seattachmentsrel FOREIGN KEY (crmid) REFERENCES vtiger_crmentity(crmid) ON DELETE CASCADE::->Cannot add or update a child row: a foreign key constraint fails (`vtiger`.`#sql-f898_1a`, CONSTRAINT `fk_2_vtiger_seattachmentsrel` FOREIGN KEY (`crmid`) REFERENCES `vtiger_crmentity` (`crmid`) ON DELETE CASCADE)
I am not really sure about the best way to address this issue - perhaps a method somewhere to remove orphan rows between "table a" and "table b" before attempting to add constraints would be the right approach?