KB-1090 "The INSERT statement conflicted with the FOREIGN KEY constraint "XXXX"" error returned when inserting data into a nested CDT

Symptoms

When inserting data containing a nested CDT, an error in the UI or on the Alerts tab in the Designer Interface, such as the following, is generated:

An error occurred while trying to write to the entity “XXXX” [id=9756441a-16e3-45f9-bb5e-957fd04cdd51@217, type=XXXX (id=8266)] (data store: XXXX). Details: org.hibernate.exception.ConstraintViolationException: could not insert: [XXXX]: com.microsoft.sqlserver.jdbc.SQLServerException: The INSERT statement conflicted with the FOREIGN KEY constraint "XXXX". The conflict occurred in database "XXXX", table "dbo.XXXX", column 'XXXX'.

Note that this error is specific to SQL Server, but similar errors can also occur in other supported databases. In the database the tables are created in a manner similar to the following:

  • The parent table has a primary key with a one-to-many relationship to the same key in the child table.
  • The child table has the above key as a foreign key.

Cause

It is likely that the data for the child table with the foreign key is being added before the data for the parent table with the primary key. A foreign key cannot have a value in its column that is not present in the primary key of the parent table.

Additionally, it's possible that there is a logic error in the expression inserting the data. When inserting data that is blank for the foreign key, a NULL value should be used rather than an empty value. The error message will contain the data that is being passed to the database. A series of empty values, which could cause this issue, would look like this:

Data: TypedValue[it=8266,v={X,{{,,}}}]

NULL, which should be used in place of empty values, would look similar to this:

Data: TypedValue[it=8266,v={X,{{<null>,<null>,<null>}}}]

Action

Make sure the table containing the primary key is built first, followed by the table with the foreign key. If inserting the data with both a primary key and foreign key present, make sure the data for the primary key is inserted prior to the data for the foreign key. If inserting blank data use NULL rather than empty values.

Affected Versions

This article applies to all versions of Appian.

Last Reviewed: January 2016

Related
Recommended