Skip to content

Uniqueness ​

Two kinds: one column whose values never repeat, and a combination of columns whose pairs never repeat.

A unique column ​

Mark it Unique in the application, or list it alone in a constraint:

yaml
Schema:
  UniqueConstraints:
    - - 7e57d004-2b97-0e7a-b45f-5387367791cd

Distinct values are reproducible like everything else, and asking for nearly all of a column's possible values is no slower than asking for a few - there is no point where it starts retrying and grinds.

Checked before the run, not during ​

Every column type reports how many distinct values it can produce. Ask for more unique rows than that and you are told when you open or validate the schema:

Requested RowCount 1000 exceeds maximum unique combinations (12) for columns: [month]

This matters most for small domains - a pick list of three values, Weekday, Boolean - where the request is impossible rather than merely slow.

Combinations ​

List several ids in one constraint and the combination is unique, while each column repeats freely:

yaml
Schema:
  UniqueConstraints:
    - - 7e57d004-2b97-0e7a-b45f-5387367791cd    # region
      - 7e57d004-2b97-0e7a-b45f-5387367791ce    # month

12 months across 4 regions gives 48 distinct pairs, and asking for a 49th row fails validation.

What cannot be unique ​

Some types decline uniqueness: their domain is large but they cannot enumerate it, so producing distinct values on demand is not possible. The application disables the option for them rather than quietly producing duplicates.

A combination needs every column to have countable values

A combination is built by drawing from each column's set of values, so every column in it must have a countable set. Guid and Double do not, and neither does a Pick List drawing several values per row. Naming one in a combination is reported before the run starts:

The unique combination [id, month] cannot be enforced, because 'id' produces an
unbounded set of values and a combination is built by drawing from each column's
values. Remove it from the constraint, or mark it unique on its own.

On its own such a column is fine - a Guid never repeats, and marking it unique works exactly as you would expect. It is only the combination that cannot be built.

Optional columns ​

A column that is IsOptional leaves some cells blank, and blanks do not count as values for uniqueness. Combining a unique constraint with a low FillProbability is usually a mistake.

Proprietary software, free to use. Licence · Privacy