Composite unique constraints

Q&A
1

Greetings,

I’m working on migrating my team’s MDM platform from SQL Server Master Data Services (MDS) to AtroCore. In MDS, we have quite a few business rules that validate record uniqueness based on multiple entity attributes (equivalent to fields in AtroCore in that they are fundamental properties that are required for all entity records).

In AtroCore, I can create individual fields with a uniqueness constraint via checking the “Unique” checkbox in the field create/edit dialog, but I haven’t been able to find a way to create a composite uniqueness constraint that spans two or more fields within an entity. I can create a composite natural key directly on the entity table, which will propagate the SQL error up to the AtroCore interface if that constraint is violated, but would prefer to implement this via application-level business logic.

Is there a way to create unique constraints based on multiple entity fields within AtroCore? Can this be accomplished via the paid Data Quality module?

2

Hello Jonathan,
In AtroCore, to create a composite key, you need to define it in the metadata on the server side:
data/metadata/entityDefs/{entity_name}.json
If this file doesn’t exist, it should be created under the server user, typically www-data.

Inside the root of the JSON file, add the following:

“uniqueIndexes”: {
“unique_relation”: [
“deleted”,
“product_id”,
“file_id”
]
}

  • "unique_relation" is the name of the index.
  • The array should list the database columns that make up the unique constraint.
  • The deleted field must always be included among them.

Then, in the root of the project, run:

php console.php sql diff --show

This command shows the SQL query that will be generated for the new index. If everything looks correct, apply the changes with:

php console.php sql diff --run

After that, run the --show command again:

php console.php sql diff --show

If the SQL query to create the index still appears, it means that there are already non-unique values in the database for the specified combination of fields.
In this case, you’ll need to inspect the database directly and resolve the duplicates manually.