What the relational model guarantees
The relational model defines what states of data are valid.
These guarantees are logical, not performance-related.
1️⃣ Set-based data (no duplicates by default)
- Relations are sets, not lists
- Rows have no inherent order
- Duplicates are eliminated conceptually
Example:
SELECT a FROM t;
Logically:
- Result is an unordered set
➡️ Order only exists if explicitly defined.
2️⃣ Entity identity via keys
The relational model guarantees:
- Every row can be uniquely identified
- Keys uniquely identify tuples
This is why:
- Candidate keys exist
- Primary keys exist
Without keys → relation is not well-defined.
3️⃣ Referential integrity
Relationships between relations are:
- Explicit
- Enforceable
- Consistent
A foreign key guarantees:
- Child rows reference existing parent rows
- Orphan rows are impossible
This is a core relational guarantee.
4️⃣ Integrity constraints
The model guarantees enforcement of:
- Domain constraints (
NOT NULL, type rules) - Key constraints (
PRIMARY KEY,UNIQUE) - Referential constraints (
FOREIGN KEY)
Invalid states are unrepresentable.
5️⃣ Declarative semantics
You specify:
- What result you want
- Not how to get it
This enables:
- Query optimization
- Execution plan changes
- Storage evolution
6️⃣ Closure (important theory point)
Relational operations:
- Take relations as input
- Produce relations as output
This allows:
- Query composition
- Subqueries
- Views
What SQL does NOT guarantee
SQL is a language, not a correctness oracle.
Many things developers assume are explicitly not guaranteed.
❌ 1️⃣ Row order (unless ORDER BY)
SELECT * FROM users;
No guaranteed order
✔ Only ORDER BY defines order
Indexes do not guarantee order.
❌ 2️⃣ Execution order of clauses
Logical SQL order ≠ execution order:
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
You cannot rely on evaluation order inside WHERE.
❌ 3️⃣ Deterministic evaluation of expressions
WHERE f(x) AND g(x)
SQL does not guarantee:
- Left-to-right evaluation
- Short-circuiting
The optimizer may reorder conditions.
❌ 4️⃣ Absence of duplicates (unless enforced)
SELECT col FROM table;
- Duplicates may appear
- SQL works on bags (multisets)
Only DISTINCT or constraints remove duplicates.
❌ 5️⃣ Consistency without constraints
SQL does not guarantee:
- Uniqueness without
UNIQUE - Referential integrity without
FOREIGN KEY - Valid ranges without
CHECK
If you don’t declare it, SQL won’t enforce it.
❌ 6️⃣ Stable execution plans
The same query:
- May use different indexes tomorrow
- May switch join algorithms
- May behave differently after stats change
Execution plans are not stable contracts.
❌ 7️⃣ Isolation without transactions
SQL does not guarantee:
- Atomicity
- Isolation
- Consistency
Unless:
BEGIN;
...
COMMIT;
Even then — isolation level matters.
Big mental model (interview gold)
| Relational Model | SQL |
|---|---|
| Defines valid data states | Defines how to query data |
| Guarantees correctness | Makes no implicit promises |
| Based on sets | Operates on multisets |
| Orderless | Order must be declared |
| Declarative | Optimizer-controlled |
One-sentence interview answer (perfect)
The relational model guarantees data correctness through keys, constraints, referential integrity, and declarative semantics, while SQL does not guarantee row order, execution order, uniqueness, consistency, or stable plans unless they are explicitly specified.
Common interview traps ❌
- Assuming index order = result order
- Relying on WHERE clause evaluation order
- Assuming SQL enforces business rules automatically
- Confusing logical correctness with physical execution