Unique Constraints

NULLs Do Not Collide Here

NULL uniqueness behavior is an explicit toy convention.

highlighted = computed this step

NULL convention

This toy SQL-like rule says NULL key cells do not collide.

NULLs do not collide here\text{NULLs do not collide here}

Read NULL key rows

The key table marks 2 rows with NULL components, and nullsCollide is no_toy_SQL_like_convention.

NULL rows=2\text{NULL rows}=2

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.

NULLs do not collide here: 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

Boundary is explicit

Different SQL systems may expose NULL uniqueness differently; this book labels the convention.

toy dialect boundary\text{toy dialect boundary}

Summary

NULL key components stay visible and do not create a collision here.

NULL visible\text{NULL visible}