Lesson 48 of 61

5 mins read

SQL NOT NULL & UNIQUE: Data Invariants

In Plain English

`NOT NULL` guarantees that a field can never contain a missing `NULL` value. `UNIQUE` guarantees that no two rows can share the exact same value in the specified column (or combination of columns). Behind the scenes, the database automatically builds a unique index to enforce `UNIQUE` constraints in $O(\log N)$ time.

The Mental Model

"`NOT NULL` is like a required field with a red asterisk on a passport application; `UNIQUE` is the passport number itself, which no two citizens can share."

Deep Dive: How It Works

NOT NULL Enforcement: Insert or update attempts with NULL are rejected before touching disk pages.

UNIQUE Index Creation: SQL engines create a backing unique B-tree index automatically to enforce uniqueness during inserts.

Composite UNIQUE: `UNIQUE(tenant_id, slug)` allows identical slugs across different organizations while keeping them unique within a tenant.

Syntax Blueprint

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL,
  CONSTRAINT unq_user UNIQUE(username)
);

Enforces column nullability and distinctness at the schema level.

Core Rules to Remember

Auto-Index Generation: Applying `UNIQUE` automatically creates a fast lookup index.

Composite Uniqueness: Multi-column unique constraints enforce multi-tenant isolation.

NULL in UNIQUE: Standard SQL allows multiple NULLs in UNIQUE columns (unless NOT NULL is also applied).

Common Beginner Traps & How to Fix Them

Mistake:Assuming `UNIQUE` prevents `NULL` entries automatically.

Why it happens: In standard SQL, NULL is unknown, so `NULL != NULL` allows multiple NULL rows unless `NOT NULL` is explicitly combined.

How to fix: Always combine `NOT NULL UNIQUE` when values must exist and be unique.

Pro Tip: Use composite `UNIQUE(org_id, slug)` for multi-tenant SaaS routing where slugs only need to be unique per organization.

Live Interactive Example

Hit Run Code to see it live

Enforcing Nullability and Distinctness with NOT NULL and UNIQUE

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Both user records were successfully stored with enforced uniqueness on email and username.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Create API Keys Table

Create a table `api_keys (id INTEGER PRIMARY KEY, key_hash TEXT NOT NULL UNIQUE, client_name TEXT NOT NULL)`.
Insert a key ("hash_991", "MobileApp") and query the table.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.