Constraints and Data Integrity
Rules the database enforces so bad data can never get in
Big Idea: A constraint is a rule attached to a table that every row must satisfy. The database checks it on every
INSERT and UPDATE and rejects anything that breaks it. Instead of hoping your application code is perfect, you push the guarantee down into the schema itself, where it holds for every client, every script and every manual edit.
By the end you will be able to:
- forbid missing values with
NOT NULL, - forbid duplicates with
UNIQUE, - enforce business rules with
CHECK, - fill in sensible defaults with
DEFAULT, - keep tables in sync with
FOREIGN KEYand itsON DELETE/ON UPDATEactions.
1. Our example schema: a small shop
Every section below queries the same tiny online-shop database. A categories table groups products, a products table holds items, a customers table holds buyers and an orders table records purchases. Create them once:
CREATE TABLE categories (
category_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(8,2) NOT NULL CHECK (price > 0),
stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
category_id INT,
CONSTRAINT fk_cat FOREIGN KEY (category_id)
REFERENCES categories(category_id)
ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
email VARCHAR(120) NOT NULL UNIQUE,
country CHAR(2) NOT NULL DEFAULT 'US'
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
total DECIMAL(10,2) NOT NULL DEFAULT 0,
CONSTRAINT fk_cust FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE ON UPDATE CASCADE
);This one CREATE TABLE block already uses every constraint type we will study: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT and FOREIGN KEY. We will take them one at a time.