Answers.Body: <p>It's not a Craft bug.</p>
  
  <p>The error means that the data in your <code>craft_relations</code> table is out of sync with the data in the your <code>craft_elements</code> table.  i.e. an ID exists in <code>craft_relations.targetId</code> that doesn't exist in <code>craft_elements.id</code> or visa-versa.</p>
  
  <p>How it got that way is hard to say, but here are some common causes:</p>
  
  <ol>
  <li>Someone manually edits database data without realizing the implications.</li>
  <li>A database was imported into a MySQL box that only supported the MyISAM storage engine, which doesn't support foreign keys, so all of the foreign keys that Craft relies on to keep data consistent between tables are silently dropped.  If this is the case, you can use <a href="https://github.com/pixelandtonic/craft-fixfks" rel="nofollow noreferrer">https://github.com/pixelandtonic/craft-fixfks</a> to try and restore Craft's foreign keys.</li>
  <li>A botched import/export script.</li>
  <li>A plugin behaving poorly.</li>
  </ol>
  
  <p>In the context of these two tables, if you want to find all rows that have a value in <code>craft_relations.targetId</code> that doesn't exist in <code>craft_elements.id</code>, the SQL would be similar to:</p>
  
  <pre><code>SELECT * FROM craft_relations r
  LEFT OUTER JOIN craft_elements e
  ON r.targetId = e.id
  WHERE e.id IS NULL
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-07-18T08:19:23.017Z
Answers.Text: Is there any way to figure out exactly what id is it, that doesn't exist in one of these  tables?
Answers.UserDisplayName: Jahanzeb Khan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-07-18T19:10:17.430Z
Answers.Text: Added some SQL to the answer.
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-07-19T08:47:06.903Z
Answers.Text: Hey @brad-bell , I ran the SQL and it found no results.  That means there aren't any **targetId** in **"craft_relations"** that don't exist in **"craft_elements"**.
  Which is I think expected, because the error is thrown when its trying to add a row in **"craft_relations"** using and **"targetId"** which doesn't exist in **"craft_elements"** table so the row is never added.
Answers.UserDisplayName: Jahanzeb Khan
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-07-19T20:08:50.427Z
Answers.Text: So you'll need to adjust the query to go in the other direction and look for results where r.targetId is null.
Answers.UserDisplayName: Brad Bell
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-07-17T20:59:03.063Z
Answers.LastActivityDate: 2018-07-18T19:10:05.910Z
Answers.LastEditDate: 2018-07-18T19:10:05.910Z
Answers.UserDisplayName: Brad Bell
Body: <p>Randomly getting the "Integrity constraint violation"  sql error on saving entry from backend.  There no apparent cause of this at least not that I can see, because the same post saves without errors when I try again.  </p>
  
  <p>Craft version is : 2.6.3017</p>
  
  <p>Here is the error and Stack trace:</p>
  
  <pre><code>[error] [system.db.CDbCommand] CDbCommand::execute() failed: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_production`.`craft_relations`, CONSTRAINT `craft_relations_targetId_fk` FOREIGN KEY (`targetId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE). The SQL statement executed was: INSERT INTO `craft_relations` (`fieldId`, `sourceId`, `sourceLocale`, `targetId`, `sortOrder`, `dateCreated`, `dateUpdated`, `uid`) VALUES (:row0_col0, :row0_col1, NULL, :row0_col3, :row0_col4, :row0_col5, :row0_col6, :row0_col7), (:row1_col0, :row1_col1, NULL, :row1_col3, :row1_col4, :row1_col5, :row1_col6, :row1_col7), (:row2_col0, :row2_col1, NULL, :row2_col3, :row2_col4, :row2_col5, :row2_col6, :row2_col7), (:row3_col0, :row3_col1, NULL, :row3_col3, :row3_col4, :row3_col5, :row3_col6, :row3_col7), (:row4_col0, :row4_col1, NULL, :row4_col3, :row4_col4, :row4_col5, :row4_col6, :row4_col7), (:row5_col0, :row5_col1, NULL, :row5_col3, :row5_col4, :row5_col5, :row5_col6, :row5_col7).
  
  [error] [exception.CDbException] CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_production`.`craft_relations`, CONSTRAINT `craft_relations_targetId_fk` FOREIGN KEY (`targetId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE) in /var/www/mysite.com/craft/app/framework/db/CDbCommand.php:358
  
  Stack trace:
  #0 /var/www/mysite.com/craft/app/etc/db/DbCommand.php(370): CDbCommand-&gt;execute(Array)
  #1 /var/www/mysite.com/craft/app/services/RelationsService.php(74): Craft\DbCommand-&gt;insertAll('craft_relations', Array, Array)
  #2 /var/www/mysite.com/craft/app/fieldtypes/BaseElementFieldType.php(291): Craft\RelationsService-&gt;saveRelations(Object(Craft\FieldModel), Object(Craft\EntryModel), Array)
  #3 /var/www/mysite.com/craft/app/services/ElementsService.php(1608): Craft\BaseElementFieldType-&gt;onAfterElementSave()
  #4 /var/www/mysite.com/craft/app/services/EntriesService.php(176): Craft\ElementsService-&gt;saveElement(Object(Craft\EntryModel))
  #5 /var/www/mysite.com/craft/app/controllers/EntriesController.php(462): Craft\EntriesService-&gt;saveEntry(Object(Craft\EntryModel))
  #6 /var/www/mysite.com/craft/app/framework/web/actions/CInlineAction.php(49): Craft\EntriesController-&gt;actionSaveEntry()
  #7 /var/www/mysite.com/craft/app/framework/web/CController.php(308): CInlineAction-&gt;runWithParams(Array)
  #8 /var/www/mysite.com/craft/app/framework/web/CController.php(286): CController-&gt;runAction(Object(CInlineAction))
  #9 /var/www/mysite.com/craft/app/framework/web/CController.php(265): CController-&gt;runActionWithFilters(Object(CInlineAction), Array)
  #10 /var/www/mysite.com/craft/app/framework/web/CWebApplication.php(282): CController-&gt;run('saveEntry')
  #11 /var/www/mysite.com/craft/app/etc/web/WebApp.php(836): CWebApplication-&gt;runController('entries/saveEnt...')
  #12 /var/www/mysite.com/craft/app/etc/web/WebApp.php(287): Craft\WebApp-&gt;_processActionRequest()
  #13 /var/www/mysite.com/craft/app/framework/base/CApplication.php(185): Craft\WebApp-&gt;processRequest()
  #14 /var/www/mysite.com/craft/app/index.php(73): CApplication-&gt;run()
  #15 /var/www/mysite.com/public/index.php(15): require_once('/var/www/mysite...')
  #16 {main}
  REQUEST_URI=/admin/entries/blog/new
  HTTP_REFERER=https://mysite.com/admin/entries/blog/new
  </code></pre>
  
  <p>Anyone ever faced this error? is this a Craft bug?</p>
  
Tags: <database><mysql>
Title: Randomly getting the "Integrity constraint violation" sql error on saving entry
ThreadText: # Randomly getting the "Integrity constraint violation" sql error on saving entry
  
  (Asked by: username_0 on 2018-07-17)
  
  Randomly getting the "Integrity constraint violation" sql error on saving entry from backend. There no apparent cause of this at least not that I can see, because the same post saves without errors when I try again.
  
  Craft version is : 2.6.3017
  
  Here is the error and Stack trace:
  
  ```
  [error] [system.db.CDbCommand] CDbCommand::execute() failed: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_production`.`craft_relations`, CONSTRAINT `craft_relations_targetId_fk` FOREIGN KEY (`targetId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE). The SQL statement executed was: INSERT INTO `craft_relations` (`fieldId`, `sourceId`, `sourceLocale`, `targetId`, `sortOrder`, `dateCreated`, `dateUpdated`, `uid`) VALUES (:row0_col0, :row0_col1, NULL, :row0_col3, :row0_col4, :row0_col5, :row0_col6, :row0_col7), (:row1_col0, :row1_col1, NULL, :row1_col3, :row1_col4, :row1_col5, :row1_col6, :row1_col7), (:row2_col0, :row2_col1, NULL, :row2_col3, :row2_col4, :row2_col5, :row2_col6, :row2_col7), (:row3_col0, :row3_col1, NULL, :row3_col3, :row3_col4, :row3_col5, :row3_col6, :row3_col7), (:row4_col0, :row4_col1, NULL, :row4_col3, :row4_col4, :row4_col5, :row4_col6, :row4_col7), (:row5_col0, :row5_col1, NULL, :row5_col3, :row5_col4, :row5_col5, :row5_col6, :row5_col7).
  
  [error] [exception.CDbException] CDbException: CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`db_production`.`craft_relations`, CONSTRAINT `craft_relations_targetId_fk` FOREIGN KEY (`targetId`) REFERENCES `craft_elements` (`id`) ON DELETE CASCADE) in /var/www/mysite.com/craft/app/framework/db/CDbCommand.php:358
  
  Stack trace:
  #0 /var/www/mysite.com/craft/app/etc/db/DbCommand.php(370): CDbCommand->execute(Array)
  #1 /var/www/mysite.com/craft/app/services/RelationsService.php(74): Craft\DbCommand->insertAll('craft_relations', Array, Array)
  #2 /var/www/mysite.com/craft/app/fieldtypes/BaseElementFieldType.php(291): Craft\RelationsService->saveRelations(Object(Craft\FieldModel), Object(Craft\EntryModel), Array)
  #3 /var/www/mysite.com/craft/app/services/ElementsService.php(1608): Craft\BaseElementFieldType->onAfterElementSave()
  #4 /var/www/mysite.com/craft/app/services/EntriesService.php(176): Craft\ElementsService->saveElement(Object(Craft\EntryModel))
  #5 /var/www/mysite.com/craft/app/controllers/EntriesController.php(462): Craft\EntriesService->saveEntry(Object(Craft\EntryModel))
  #6 /var/www/mysite.com/craft/app/framework/web/actions/CInlineAction.php(49): Craft\EntriesController->actionSaveEntry()
  #7 /var/www/mysite.com/craft/app/framework/web/CController.php(308): CInlineAction->runWithParams(Array)
  #8 /var/www/mysite.com/craft/app/framework/web/CController.php(286): CController->runAction(Object(CInlineAction))
  #9 /var/www/mysite.com/craft/app/framework/web/CController.php(265): CController->runActionWithFilters(Object(CInlineAction), Array)
  #10 /var/www/mysite.com/craft/app/framework/web/CWebApplication.php(282): CController->run('saveEntry')
  #11 /var/www/mysite.com/craft/app/etc/web/WebApp.php(836): CWebApplication->runController('entries/saveEnt...')
  #12 /var/www/mysite.com/craft/app/etc/web/WebApp.php(287): Craft\WebApp->_processActionRequest()
  #13 /var/www/mysite.com/craft/app/framework/base/CApplication.php(185): Craft\WebApp->processRequest()
  #14 /var/www/mysite.com/craft/app/index.php(73): CApplication->run()
  #15 /var/www/mysite.com/public/index.php(15): require_once('/var/www/mysite...')
  #16 {main}
  REQUEST_URI=/admin/entries/blog/new
  HTTP_REFERER=https://mysite.com/admin/entries/blog/new
  
  ```
  
  Anyone ever faced this error? is this a Craft bug?
  
  **Tags:** database, mysql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2018-07-17. Score: 5
  
  It's not a Craft bug.
  
  The error means that the data in your `craft_relations` table is out of sync with the data in the your `craft_elements` table. i.e. an ID exists in `craft_relations.targetId` that doesn't exist in `craft_elements.id` or visa-versa.
  
  How it got that way is hard to say, but here are some common causes:
  
  1. Someone manually edits database data without realizing the implications.
  2. A database was imported into a MySQL box that only supported the MyISAM storage engine, which doesn't support foreign keys, so all of the foreign keys that Craft relies on to keep data consistent between tables are silently dropped. If this is the case, you can use <https://github.com/pixelandtonic/craft-fixfks> to try and restore Craft's foreign keys.
  3. A botched import/export script.
  4. A plugin behaving poorly.
  
  In the context of these two tables, if you want to find all rows that have a value in `craft_relations.targetId` that doesn't exist in `craft_elements.id`, the SQL would be similar to:
  
  ```
  SELECT * FROM craft_relations r
  LEFT OUTER JOIN craft_elements e
  ON r.targetId = e.id
  WHERE e.id IS NULL
  
  ```
  
  > **Comments:**
  > **username_0**: Is there any way to figure out exactly what id is it, that doesn't exist in one of these  tables?  
  > **username_1**: Added some SQL to the answer.  
  > **username_0**: Hey @username_1 , I ran the SQL and it found no results.  That means there aren't any **targetId** in **"craft_relations"** that don't exist in **"craft_elements"**. Which is I think expected, because the error is thrown when its trying to add a row in **"craft_relations"** using and **"targetId"** which doesn't exist in **"craft_elements"** table so the row is never added.  
  > **username_1**: So you'll need to adjust the query to go in the other direction and look for results where r.targetId is null.  
  
  ---
  
  