Python SQL NOT NULL & UNIQUE: Data Invariants
`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.
"`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
Common Beginner Traps & How to Fix Them
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.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
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.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.