Here are four rows from a product catalogue. Two of them are a problem, two of them are fine, and the difference is not visible unless you know which rule is running.
Two rules are on that entity. One says the SKU is unique across the catalogue. The other says a supplier may not list the same SKU twice. The first pair breaks only the first rule, because although the SKU repeats, it repeats under two different suppliers. The second pair breaks both, because it is the same supplier twice.
Which of those two is a real problem depends entirely on your business, and that is the point. A catalogue that resells other manufacturers' parts will see the same SKU under several suppliers all day long and should not have the first rule at all. A catalogue of own-brand products should. The engine does not guess; you tell it, and it is two clicks apart.
Single-column uniqueness is the easy half
Codes are unique in Primentra without anyone configuring it, and required fields, data types and domain lists have been attribute properties for a long time. So a single-column uniqueness rule is only interesting when it is on a column that is not the code: a SKU, a VAT number, an employee's payroll reference, an asset tag. Those are the columns people assume are unique and never check.
One detail matters more than it sounds. When a rule evaluates uniqueness it looks at the whole entity, not at the rows currently being saved. That is deliberate. Check only the row in front of you and a duplicate's partner is invisible, so a single-row save could never detect a collision with something already in the table. Because the check spans everything, both halves of a duplicate pair get reported, which is what you want when you are trying to work out which of the two is the real record.
Combination keys are the ones people rebuild first
Ask a team migrating off MDS which rule they need working on day one and it is usually this one. Not because it is complicated, but because it encodes something nobody wrote down: the actual identity of a record. Supplier plus SKU. Cost centre plus account. Site plus asset tag. Employee plus effective date.
The Combination unique template takes a list of columns and treats their values as one key. There is no limit of two; a three or four column key is the same rule with more columns in the list. Internally the values are concatenated with a separator that cannot occur in the data, so “AB” plus “C” never collides with “A” plus “BC” the way a naive concatenation would.
The blanks, which is where this gets interesting
Half a million rows into a migration you will have rows where the key is not filled in yet. What should a uniqueness rule do with fifty rows that all have no VAT number?
Nothing, is the answer. A rule is asserted only against rows that hold a value in the column it targets, so two blanks are never duplicates. For a combination key the same principle applies to every member: a row has to have a value in all of the key's columns before it is compared with anything. A product with a SKU but no supplier is not in the running.
If you have reached for a unique index before, this is the opposite of what you are used to, and the difference is worth having in front of you.
None of this makes the index wrong. It makes it a different tool. An index protects the table; a rule explains the data to the person editing it. On a master data set being cleaned up by humans, the explanation is usually the part you were missing.
Warning severity, for when you are not sure yet
Turning on a uniqueness rule at error severity against a table that already has duplicates is how you discover, loudly, that it has duplicates. Nothing can be saved until they are gone, including the edits somebody needs to make to get rid of them.
The way round it is to start the rule as a warning. Every offending row is marked and appears under the grid's warnings filter, nothing is blocked, and the stewards can work through the list. When the list is empty, switch the rule to error and it stays empty. Before you do any of that, run Test rule from the editor: it evaluates against the data you already have and tells you the size of the problem without writing anything or even saving the rule.
What this does not do
A uniqueness rule compares values exactly. “Acme Tools BV” and “Acme Tools B.V.” are two different suppliers as far as it is concerned, and no amount of rule configuration will make them one. Catching those is fuzzy matching, which is a different mechanism and is on the roadmap rather than in the product.
It also has nothing to say about duplicates that already exist. Deciding which of two matched records survives, and which of their field values wins, is survivorship, and it happens at merge time rather than at write time. A uniqueness rule works at the front door. It is worth being clear about which of the three problems you actually have before going looking for a feature.
For how uniqueness fits with the rest of the engine — the operators, the severities, and which write path enforces what — the full guide to business rules is the place to start.
Common questions
What is a composite unique key?
A rule that two or more columns must be unique together rather than on their own. A supplier can appear many times and a SKU can appear many times, but the same supplier with the same SKU only once.
Do empty values count as duplicates?
No. The rule is asserted only against rows holding a value in every column of the key. Two blanks never collide, and a row missing one member of a combination key drops out of the check rather than colliding with every other incomplete row.
Why not just use a unique index?
An index gives you a violation, not an explanation, and it treats NULL as a value. A rule carries its own message, marks the cell, reports both sides of the duplicate, and can be set to warning so a suspected duplicate is flagged rather than refused.
Is this deduplication?
No. This stops a duplicate being created. Deduplication finds inexact duplicates that already exist, and survivorship decides which values win when two records merge. Different problems, different moments.
Find out how many duplicates you already have
Build the combination key you think identifies a record, press Test rule, and read the number. Nothing is written and the rule does not have to be saved. Primentra runs on your own SQL Server, deploys in a day, and costs €7,500 per year flat, with a 60-day trial.