<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>

CC BY-SA 4.0
2018-07-18T08:19:23.017Z
Is there any way to figure out exactly what id is it, that doesn't exist in one of these  tables?
Jahanzeb Khan
CC BY-SA 4.0
2018-07-18T19:10:17.430Z
Added some SQL to the answer.
Brad Bell
CC BY-SA 4.0
2018-07-19T08:47:06.903Z
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.
Jahanzeb Khan
CC BY-SA 4.0
2018-07-19T20:08:50.427Z
So you'll need to adjust the query to go in the other direction and look for results where r.targetId is null.
Brad Bell
CC BY-SA 4.0
2018-07-17T20:59:03.063Z
2018-07-18T19:10:05.910Z
2018-07-18T19:10:05.910Z
Brad Bell
<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>

<database><mysql>
Randomly getting the "Integrity constraint violation" sql error on saving entry
# 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:

Someone manually edits database data without realizing the implications.
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.
A botched import/export script.
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 @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.  
> **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.  

---

