Constraints

Constraints are the rules that the database engine enforces to ensure the integrity of your data. They prevent users from accidentally (or intentionally) entering garbage data into the system.

Definition: The 5 Essential Constraints

PRIMARY KEY: Uniquely identifies each row. Cannot be NULL.
FOREIGN KEY: Ensures that a value in Table A exists in Table B (Referential Integrity).
NOT NULL: Ensures a column cannot have a NULL value.
UNIQUE: Ensures all values in a column are different (but allows one NULL).
CHECK: Ensures values meet a specific logical condition (e.g., Price > 0).

Context: Why Constraints?

Without constraints, your database becomes a “Data Swamp.”

You could have an Order for a Customer who doesn’t exist.
You could have two users with the same Email.
You could have a Product with a negative Weight.

Constraints move the “validation logic” from the application code into the database, making it impossible to bypass.

Detailed Explanation: Syntax

CREATE TABLE Students (
  ID          INT PRIMARY KEY,
  Email       VARCHAR(255) UNIQUE NOT NULL,
  Age         INT CHECK (Age >= 18),
  MajorID     INT REFERENCES Majors(ID) -- Foreign Key
);

Example: Cascading Deletes

A Foreign Key doesn’t just check for existence; it also defines what happens when data is deleted.

Imagine a Customers table and an Orders table. If you delete a customer, what happens to their 100 orders?

ON DELETE RESTRICT (Default): The database prevents you from deleting the customer until you delete their orders first.
ON DELETE CASCADE: The database automatically deletes all 100 orders when you delete the customer.
ON DELETE SET NULL: The database keeps the orders but sets the CustomerID column to NULL.

ALTER TABLE Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (CustomerID)
REFERENCES Customers(ID)
ON DELETE CASCADE;

Under the Hood: Constraint Checking

How does a constraint affect performance?

1. Indexing

When you create a PRIMARY KEY or UNIQUE constraint, the database automatically creates a B-Tree Index on that column. It has to do this because the only way to quickly check if a value is unique is to look it up in a sorted index.

2. Validation Overhead

Every time you INSERT or UPDATE, the database must check your constraints.

If you have 20 Foreign Keys on a single table, every insert requires 20 “lookups” in other tables to verify existence.
While this is usually very fast, it does add a small amount of overhead to write operations.

3. Immediate vs. Deferred

In some databases, you can “Defer” constraints. This means the database waits until the very end of a transaction (see next chapter) to check the rules. This is useful for complex circular relationships.