Lesson 47 of 61

5 mins read

SQL Constraints Overview: Relational Integrity

In Plain English

Constraints are declarative validation rules applied to columns or entire tables. They instruct the database engine to reject any `INSERT` or `UPDATE` operation that violates business invariants. Enforcing integrity at the database layer guarantees data reliability even if multiple application services or manual SQL scripts access the database.

The Mental Model

"Constraints are like the security guards and turnstiles at a subway station: they ensure nobody enters without a valid ticket or through an unauthorized gate."

Deep Dive: How It Works

Declarative Integrity: The database enforces invariants automatically across all transactions.

Types of Constraints: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.

Table vs Column Level: Single-column constraints are declared inline; multi-column composite constraints are declared at table level.

Syntax Blueprint

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL,
  status TEXT CHECK(status IN ('pending', 'paid', 'cancelled')),
  amount REAL DEFAULT 0.0 CHECK(amount >= 0)
);

Applies integrity constraints directly to table column definitions.

Core Rules to Remember

Single Source of Truth: Integrity rules reside in the database, protecting data against buggy client applications.

Immediate Rejection: Transactions violating constraints fail immediately with specific error codes.

Performance Advantage: The query optimizer uses constraint guarantees to eliminate redundant filter evaluations.

Common Beginner Traps & How to Fix Them

Mistake:Relying exclusively on frontend or API validation without database constraints.

Why it happens: Direct DB migrations, batch scripts, or race conditions will eventually insert corrupt rows.

How to fix: Always duplicate critical business invariants as database-level constraints.

Pro Tip: Name your constraints explicitly when possible (e.g. `CONSTRAINT chk_price_positive CHECK (price > 0)`) for clear debugging error messages.

Live Interactive Example

Hit Run Code to see it live

Enforcing Data Integrity with Constraints

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: Table verified valid insertions adhering to NOT NULL, UNIQUE, and CHECK constraints.

Your Turn: Micro Challenge

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

Micro Exercise

Define Constrained Accounts Table

Create a table `accounts` with `id INTEGER PRIMARY KEY`, `email TEXT NOT NULL UNIQUE`, and `balance REAL CHECK(balance >= 0)`.
Insert a valid account ("[email protected]", 100.0) and select it.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.