Unique Constraints

Duplicate Non-NULL Violation

Duplicate present keys violate UNIQUE.

highlighted = computed this step

Duplicate present keys

Two present equal keys violate the UNIQUE rule.

duplicate non-NULL key\text{duplicate non-NULL key}

Read the duplicate

The violation points to source rows 0 and 2.

violations=1\text{violations}=1

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.

Duplicate non-NULL violation: 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

Both keys are present

The duplicate key has no NULL component, so it collides.

present keys collide\text{present keys collide}

Summary

A duplicate non-NULL key makes the table invalid for that rule.

tableValid = no\text{tableValid = no}