SQL NULL Uniqueness Pack

A unique constraint does not always mean "only one blank value."

In standard SQL, NULL means unknown rather than a normal value. Many databases therefore allow several rows with NULL in a unique column because unknown is not equal to unknown. The exact behaviour and available fixes depend on the database engine, so name the engine before copying a constraint from a blog post.

Choose the business rule first

Ask what the optional field means:

• If every row must have the value, use NOT NULL plus UNIQUE.
• If many rows may omit it but supplied values must be unique, a normal unique constraint may already be correct.
• If only one row may have a missing value, use the engine's supported nulls-not-distinct feature or an explicit expression/index policy.
• If uniqueness applies only to active rows, use a partial index with the exact predicate.

Do not drop in a sentinel such as an empty string without proving it can never be real data. Sentinels make application code and migrations carry a hidden rule forever.

Migration sequence

1. Query the existing table for duplicate non-null values and duplicate combinations.
2. Decide how to handle rows that violate the intended new rule. Put ambiguous cases in a review queue.
3. Add or build the new index using the safest online method supported by the database.
4. Validate it before removing the old protection.
5. Keep fixtures for every supported null and non-null combination.

For multi-column uniqueness, write the examples down. A constraint on (tenant_id, external_id) behaves differently when one or both columns can be null. An expression index that replaces null with a sentinel also changes that meaning.

Verification checklist

Use real database tests, not an in-memory substitute:

1. Insert two rows with the same non-null identity. The second must fail.
2. Insert the allowed null combinations. Their result must match the written policy.
3. Insert every disallowed null combination. Each must fail.
4. Run the migration against a fixture containing old duplicates and prove it stops for review.
5. Confirm the query planner and application error handling use the new constraint.
6. Test rollback while both old and new application versions are running.

The dry-run starter takes the schema, database engine, identity rules, duplicate fixtures, and migration limits. It returns the null-semantics decision, constraint design, cleanup queue, migration plan, and engine-specific tests without applying any database change.

Download the runnable pack

• Complete workflow pack ZIP
• Importable n8n workflow
• GitHub validation workflow
• GitHub manual run workflow
• Docker Compose file
• Fictional sample payload
• Expected safe dry-run result

Use NOTNULL when you want the reel-specific implementation notes.