Answers.Body: <p>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</p>
  
  <p>This will have to be done for every upgrade with a db that does not include the FK.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-06-27T21:53:02.500Z
Answers.LastActivityDate: 2018-06-27T21:53:02.500Z
Answers.UserDisplayName: Rdarris
Answers.Body: <p>That doesn't really make sense because there are no updates to that table in upgrading from 4.7.30 to 5.0.2</p>
  
  <p>Here is how that table should look</p>
  
  <p><code>
  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;
  </code></p>
  
  <p>What is in civicrm.domain.version?</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-05-02T15:00:14.313Z
Answers.Text: The version of Civi is actually 4.7.13
Answers.UserDisplayName: Rdarris
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-05-01T02:19:08.727Z
Answers.LastActivityDate: 2018-05-01T02:19:08.727Z
Answers.UserDisplayName: user4278
Body: <p>After running the command to upgrade the database I got a constraint violation error. </p>
  
  <p>ERROR USERINFO: ALTER TABLE <code>civicrm_case</code> ADD CONSTRAINT <code>FK_civicrm_case_case_type_id</code> FOREIGN KEY (<code>case_type_id</code>) REFERENCES <code>civicrm_case_type</code> (<code>id</code>) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails...</p>
  
  <p>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)</p>
  
  <p>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. </p>
  
  <p>How do I prevent the migration from deleting the foreign key?</p>
  
  <p>This is my civicrm_case:</p>
  
  <p>CREATE TABLE <code>civicrm_case</code> (</p>
  
  <blockquote>
    <p><code>id</code> int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Unique Case ID',
    <code>case_type_id</code> int(10) unsigned NOT NULL COMMENT 'FK civicrm_case_type.id',
    <code>subject</code> varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'Short name of the case.',
    <code>start_date</code> date DEFAULT NULL COMMENT 'Date on which given case starts.',
    <code>end_date</code> date DEFAULT NULL COMMENT 'Date on which given case ends.',
    <code>details</code> text COLLATE utf8_unicode_ci COMMENT 'Details about the meeting (agenda, notes, etc).',
    <code>status_id</code> int(10) unsigned NOT NULL COMMENT 'Id of case status.',
    <code>is_deleted</code> tinyint(4) DEFAULT '0',
    PRIMARY KEY (<code>id</code>),
    KEY <code>index_case_type_id</code> (<code>case_type_id</code>),
    KEY <code>index_is_deleted</code> (<code>is_deleted</code>)
    ) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;</p>
  </blockquote>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2018-05-01T01:18:44.547Z
Comments.Text: That doesn't really make sense because there are no updates to that table in upgrading from 4.7.30 to 5.0.2
Comments.UserDisplayName: user4278
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-02T19:37:50.500Z
Comments.Text: have you moved the db (eg by taking a backup and reading in to a new or other db)?
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-05-03T14:51:22.600Z
Comments.Text: I imported a back up of the db after the db upgrade failed. I also tried importing the civicrm db from the live site.
Comments.UserDisplayName: Rdarris
Tags: <upgrade><database>
Title: CiviCRM Cannot add or update a child row: a foreign key constraint fails error after upgrading from 4.7.30 to 5.0.2
ThreadText: # CiviCRM Cannot add or update a child row: a foreign key constraint fails error after upgrading from 4.7.30 to 5.0.2
  
  (Asked by: username_0 on 2018-04-30)
  
  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;
  
  **Tags:** upgrade, database
  
  > **Comments:**
  > **username_1**: That doesn't really make sense because there are no updates to that table in upgrading from 4.7.30 to 5.0.2  
  > **username_2**: have you moved the db (eg by taking a backup and reading in to a new or other db)?  
  > **username_0**: I imported a back up of the db after the db upgrade failed. I also tried importing the civicrm db from the live site.  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2018-06-27. Score: 2 | Accepted answer
  
  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 by username_1 on 2018-05-01. Score: 1
  
  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?
  
  > **Comments:**
  > **username_0**: The version of Civi is actually 4.7.13  
  
  ---
  
  