Time Complexity: Column constraints (NOT NULL, UNIQUE, CHECK)
O(log n)
0
0
Column constraints (NOT NULL, UNIQUE, CHECK) in PostgreSQL - Time & Space Complexity
Understanding Time Complexity
When we add column constraints like NOT NULL, UNIQUE, or CHECK, the database must verify these rules for each row.
We want to understand how the time to insert or update data changes as the table grows.
Scenario Under Consideration
Analyze the time complexity of inserting a row with constraints.
INSERT INTO users (email, age) VALUES ('user@example.com', 25);
-- email has UNIQUE constraint (creates B-tree index automatically)
-- age has CHECK (age >= 18)
-- email and age are NOT NULL
This code inserts a new user while the database checks constraints on the data.
Identify Repeating Operations
Look at what the database does repeatedly when inserting rows.
- Primary operation: Checking the UNIQUE constraint requires an index lookup on existing emails.
- How many times: This lookup happens once per insert using the B-tree index.
How Execution Grows With Input
As the table grows, checking constraints takes more effort.
| Input Size (n) | Approx. Operations |
|---|---|
| 10 | About 3 index comparisons for UNIQUE |
| 100 | About 7 index comparisons for UNIQUE |
| 1000 | About 10 index comparisons for UNIQUE |
Pattern observation: The UNIQUE check grows logarithmically with the number of rows.
Final Time Complexity
Time Complexity: O(log n)
This means the time to insert grows logarithmically with the number of rows in the table.
Common Mistake
[X] Wrong: "Checking UNIQUE constraints is instant no matter the table size."
[OK] Correct: The database must perform an index lookup to ensure uniqueness, which takes more time as the table grows.
Interview Connect
Understanding how constraints affect performance helps you design better databases and write efficient queries.
Self-Check
"What are the time complexities of the NOT NULL and CHECK constraints?"