Imagine a bank where a customer can have a negative account balance for a "Savings" account, or an e-commerce site where an order is placed for a product that doesn't exist. These are nightmares for developers and business owners alike.
In the world of databases, SQL Constraints are the invisible walls that prevent these nightmares. They are the rules you define on your tables to ensure that the data being entered is accurate, reliable, and consistent.
Why Constraints Matter
Without constraints, your database is just a "bucket" of data. With constraints, it becomes a "source of truth." Constraints help you:
- Prevent duplicate data (e.g., two users with the same email).
- Maintain relationships (e.g., ensuring an order always belongs to a valid customer).
- Enforce business rules (e.g., a "price" column must always be greater than zero).
1. PRIMARY KEY: The Unique Anchor
Every table should have a PRIMARY KEY. It uniquely identifies each record in the table. It must contain unique values and cannot contain NULL values.
CREATE TABLE users (
id INT PRIMARY KEY,
username VARCHAR(50)
);
2. NOT NULL: No Empty Spaces
The NOT NULL constraint ensures that a column cannot have a NULL value. Use this for essential information like a user's name or an order date.
3. UNIQUE: Preventing Duplicates
While a primary key is unique, sometimes you need other columns to be unique too—like an email address or a social security number. The UNIQUE constraint ensures all values in a column are different.
4. FOREIGN KEY: Linking Tables
A FOREIGN KEY is a column (or collection of columns) in one table that refers to the PRIMARY KEY in another table. This ensures Referential Integrity.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id)
);
If you try to delete a user who has active orders, the database will stop you (unless you've specified ON DELETE CASCADE), preventing "orphan" records.
5. CHECK: Enforcing Business Logic
The CHECK constraint allows you to specify a condition that must be met for the data to be valid. This is incredibly powerful for enforcing domain-specific rules.
6. DEFAULT: Safe Fallbacks
The DEFAULT constraint provides a default value for a column when none is specified during an INSERT. This is great for setting timestamps or initial status values.
CREATE TABLE posts (
id INT PRIMARY KEY,
title VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Strategy: When to Use Which?
| Goal | Constraint |
|---|---|
| Identify rows uniquely | PRIMARY KEY |
| Link two tables | FOREIGN KEY |
| Prevent empty values | NOT NULL |
| Prevent duplicate values | UNIQUE |
| Enforce range/list rules | CHECK |
| Set a fallback value | DEFAULT |
Conclusion
Constraints are your first line of defense against data corruption. While it’s tempting to handle all validation in your application code, putting it in the database ensures that no matter how the data is accessed—via your app, an admin tool, or a manual script—the rules are ALWAYS enforced.
Design your tables with constraints today, and your future self (and your DBAs) will thank you!
Practice Challenge
Try creating a students table that requires a GPA to be between 0.0 and 4.0 and provides a default enrollment_year of 2024. Use the concepts we learned above!