Unique Constraints

Table before the Rule

Visible rows come before a UNIQUE rule.

highlighted = computed this step

Table before the rule

Start with visible rows before UNIQUE checks any key.

rows first\text{rows first}

Read the table

The pinned table has 6 rows.

rows=6\text{rows}=6

UNIQUE constraint examples are tiny finite table checks; SQL dialect completeness, index implementation, locking, optimizer behavior, product behavior, and performance claims are out of scope.

Table before the rule: base rowsidemailregionplan1adaeastbasic2beneastpro3adawestbasic4NULLeasttrial5NULLwesttrial6cyeastbasic UNIQUE rulenamecolumnsuq_email(email) UNIQUE keys by source rowsourceinputkeykeyStatus0(1, ada, east, basic)(ada)checkable1(2, ben, east, pro)(ben)checkable2(3, ada, west, basic)(ada)checkable3(4, NULL, east, trial)(NULL)NULL_component_does_not_collide4(5, NULL, west, trial)(NULL)NULL_component_does_not_collide5(6, cy, east, basic)(cy)checkable UNIQUE violationsleftrightkeyreason02(ada)duplicate_non_NULL_key Insert candidate checkfieldvaluepresentno UNIQUE factsfactvaluerowCount6constraintNameuq_emailkeyColumnCount1violationCount1tableValidnonullsCollideno_toy_SQL_like_conventioncandidateDecisionnone

Keep row positions

The rule table later points violations back to source rows.

source rows visible\text{source rows visible}

Summary

UNIQUE is checked against these exact rows.

constraint from rows\text{constraint from rows}