Foreign Keys and how to deal with them

Hi all,

If you have ever done any development in Creatio, chances are you've come across the "Foreign Key Constraint" error. There are many ways to stumble upon this error. Today, I'm going to teach you how to understand what exactly went wrong and fix it yourself, without having to reach out to the support team.

While fairly common (at least from my experience), it is also one of the easiest to diagnose and fix.

Find more articles and tips on my blog

It may require executing some SQL. I'll provide all the necessary scripts. The scripts I'll provide in this article won't mess up your database, so there's no need to worry. But if you're unsure about what you're doing, take extra care, especially with scripts from the internet.

In this article, I'll only cover PostgreSQL and MS SQL as they're much more common. The general procedure for Oracle is the same; you'll only need to adapt the queries to Oracle.

There'll be a short summary at the end if you don't have time to read the whole article.

What is a foreign key constraint error?

In simple terms, it's the database's way of saying that the provided value doesn't "fit."

Think of it as trying to fit a ball into a square socket in the famous children's toy. The database expects a certain kind of value and receives something that doesn't fit.

For example, imagine you're setting the “Account” field for a “Contact.” You can only choose an account that actually exists in the system. If you try to pick one that’s not there, you’ll run into this constraint error.

the toy in question

Now that you know what it is, let’s go over how to deal with it.

Depending on the database type, you'll receive something like this :

Terrasoft.Common.DbOperationException: Npgsql.PostgresException (0x80004005): 23503: insert or update on table "MyTable" violates foreign key constraint "FK8k9PLTrOuC7utdecSZ0EIBziLQo" 

(for PostgreSQL DB)

or

The statement has been terminated. ---> System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FKrsyYBKm1NBl8dx2I8gdwpDVlpuw". The conflict occurred in database "dbo.MyDatabase", table "dbo.MyTable", column 'Id'. 

(for MS SQL server)

The name of the key is crucial for fixing the issue. It's much easier to do if you have direct access to the database (which may not be the case for most cloud-based instances), but if you don't - that's not a problem.

I prepared a package which you can download( right click -> save link as...) here. If you do, you can skip this whole section.

Since we don't have access to the database and we still need to get some data from the database, we'll create a view. To create a view, you need to create a new SQL script in your configuration:

1. Select any unlocked package ( preferably not Custom, if you plan to move this functionality to other environments) :

2. Select your DBMS type (PostgreSQL or MS SQL):

If you're unsure which DMBS your database is, create scripts for both and install them. One of them will fail - that's okay.

Paste the following script

for PostgreSQL:

DROP VIEW IF EXISTS "YSVwForeignKeys";

CREATE VIEW "YSVwForeignKeys" AS
SELECT 
 	uuid_generate_v4() AS "Id",
    tc.constraint_name AS "Name",
    tc.table_name AS "ParentTable",
    kcu.column_name AS "ParentColumn",
    ccu.table_name AS "ReferencedTable"
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 tc.constraint_name = ccu.constraint_name
WHERE 
    tc.constraint_type = 'FOREIGN KEY'; 

3. Create the object

Now you need to create the object called exactly as the view. In our case it's YSVwForeignKeys. Create the columns : "Id" - unique identifier, "Name", "ParentTable", "ParentColumn", "ReferencedTable" - text(250)

4. Add the object to the lookups section

Navigate to Lookups and click "New Lookup". Select the object created on the previous step

5. Find the key by name

Find the key by the name you copied from the error message:

Search results

Now, here's the result. Let's break down what we're seeing:

1. Name: that's the name of the key, pretty straightforward.
2. Parent Table: that's the name of the table, which is bound by the foreign key. In other words, that's the table that you're trying to insert or update data in.
3. Parent Column: that's the name of the column you're trying to insert or update data to. It belongs to the Parent Table. Notice that since it's a lookup column, the name always ends with Id. If you go to the object configuration, you'll see that the column is called Parent (not ParentId).
4. Referenced Table: that's the table from which you can select values.

So to sum it up, you need to read it like this:

"I can only set Id's from Table Account into column Account.ParentId."

If you were to set a value from another table, you'd see a message:

insert or update on table "Account" violates foreign key constraint "FK4vuAoQFpJNm3KVrfzlDHOztOhk"

6. Fix your logic.

Now with that knowledge of what data exactly you're setting wrong, it's much easier to find the error in your logic, be it a business process or any other logic.

This tutorial won't cover 100% of the situations when you see this error, but it'll allow you to fix the easier ones within minutes, without having to wait for the support team to help you!

TL;DR:

1. Install the package
2. Open "Foreign Keys" lookup
3. Take the foreign key name from the error and find it in the lookup
4. Fix your logic.