Unique Constraints

Try Insert Candidates

Candidate rows are accepted or rejected by the same key rule.

highlighted = computed this step

Try insert candidates

A candidate row is checked against the existing UNIQUE keys.

candidate check\text{candidate check}

Accepted candidate

The first candidate is accepted because it does not collide with an existing key.

candidate accepted\text{candidate accepted}

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.

Insert candidate accepted: 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 checkfieldvaluepresentyescandidate(7, dax, east, trial)key(dax)collidesWith()decisionaccepted UNIQUE factsfactvaluerowCount6constraintNameuq_emailkeyColumnCount1violationCount1tableValidnonullsCollideno_toy_SQL_like_conventioncandidateDecisionaccepted

Rejected candidate

The second candidate is rejected because its key collides.

candidate rejected\text{candidate rejected}

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.

Insert candidate rejected: 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 checkfieldvaluepresentyescandidate(8, ada, south, trial)key(ada)collidesWith(0, 2)decisionrejected UNIQUE factsfactvaluerowCount6constraintNameuq_emailkeyColumnCount1violationCount1tableValidnonullsCollideno_toy_SQL_like_conventioncandidateDecisionrejected

Summary

The candidate check uses the same recomputed key comparison.

same rule\text{same rule}