Troubleshooting Constraint Violation Errors in Creatio

If you’re experiencing constraint violation errors when debugging code or processes in Creatio, it can sometimes be difficult to understand where exactly the problem lies. This article will outline how to troubleshoot these types of errors.

Background

In Creatio, when you add a lookup to an entity object, it adds a database-level constraint that enforces that values put in the lookup column actually exist on the related table. It also uses these constraints to prevent deleting records where the record is referenced in a lookup column of some other table.

Constraint Violation Errors

As an example of a constraint violation error, I have a process that receives Id’s for related records and then attempts to insert a record using an Add Data element in the process, using the related record Ids that were passed in. My process is receiving the following error in the process log:

The error states:

Terrasoft.Common.DbOperationException: 23503: insert or update on table “UsrUserActivity” violates foreign key constraint “FKcz7zFBD4sHNstTVOPawt3vXRHw”

Troubleshooting Constraint Violation Errors

The key in understanding what is causing the issue is determining what this constraint mentioned in the error is for. Meaning, which column value in my Add Data is causing the problem (it might have several different lookup columns being set). In the example above, the value “FKcz7zFBD4sHNstTVOPawt3vXRHw” is the name of the constraint being violated. We can use that to see what column the constraint is for using the following SQL (the following will work on MSSQL and Postgresql databases):

SELECT 
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'
and tc.constraint_name = 'FKcz7zFBD4sHNstTVOPawt3vXRHw'

This will produce the following result when run via SQL Executor:

If you look at the results for this query, it is telling us that the issue is with the contact Id value in my Add Data element. I am adding data to an entity named UsrUserActivity and putting a value in the UsrContact lookup column and this value does not exist on the related Contact table in the Id column.

With this information, I now know that the violation is coming from the value being used in my objects UsrContact column.