CiviCRM Cannot add or update a child row: a foreign key constraint fails error after upgrading from 4.7.30 to 5.0.2

Question

After running the command to upgrade the database I got a constraint violation error.

ERROR USERINFO: ALTER TABLE civicrm_case ADD CONSTRAINT FK_civicrm_case_case_type_id FOREIGN KEY (case_type_id) REFERENCES civicrm_case_type (id) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails...

I tried adding the foreign key in the database but that did not work. I got this error: MySQL ERROR 1005: Can’t create table (errno: 150)

The Foreign Key exists on the my live site's db so I imported a db backup from the lives site. Ran the db update command again and got the same error.

How do I prevent the migration from deleting the foreign key?

This is my civicrm_case:

CREATE TABLE civicrm_case (

id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Case ID', case_type_id int(10) unsigned NOT NULL COMMENT 'FK civicrm_case_type.id', subject varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Short name of the case.', start_date date DEFAULT NULL COMMENT 'Date on which given case starts.', end_date date DEFAULT NULL COMMENT 'Date on which given case ends.', details text COLLATE utf8_unicode_ci COMMENT 'Details about the meeting (agenda, notes, etc).', status_id int(10) unsigned NOT NULL COMMENT 'Id of case status.', is_deleted tinyint(4) DEFAULT '0', PRIMARY KEY (id), KEY index_case_type_id (case_type_id), KEY index_is_deleted (is_deleted) ) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Answer 1

A workaround to this is to comment out the SQL lines in the file that drop, modify and rebuild the FK. /CRM/Upgrade/Incremental/sql/4.6.18.mysql.tlp

This will have to be done for every upgrade with a db that does not include the FK.

Answer 2

That doesn't really make sense because there are no updates to that table in upgrading from 4.7.30 to 5.0.2

Here is how that table should look

CREATE TABLE `civicrm_case` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Case ID', `case_type_id` int(10) unsigned NOT NULL COMMENT 'FK to civicrm_case_type.id', `subject` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Short name of the case.', `start_date` date DEFAULT NULL COMMENT 'Date on which given case starts.', `end_date` date DEFAULT NULL COMMENT 'Date on which given case ends.', `details` text COLLATE utf8_unicode_ci COMMENT 'Details about the meeting (agenda, notes, etc).', `status_id` int(10) unsigned NOT NULL COMMENT 'Id of case status.', `is_deleted` tinyint(4) DEFAULT '0', `created_date` timestamp NULL DEFAULT NULL COMMENT 'When was the case was created.', `modified_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'When was the case (or closely related entity) was created or modified or deleted.', PRIMARY KEY (`id`), KEY `index_case_type_id` (`case_type_id`), KEY `index_is_deleted` (`is_deleted`), CONSTRAINT `FK_civicrm_case_case_type_id` FOREIGN KEY (`case_type_id`) REFERENCES `civicrm_case_type` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What is in civicrm.domain.version?