Keys, Constraints, and Relationships

Constraints are rules the database enforces automatically, protecting your data from becoming inconsistent or invalid.

PRIMARY KEY

Uniquely identifies each row in a table. A table can have only one primary key, it cannot contain NULL, and its values must be unique:

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

FOREIGN KEY

Links a column in one table to the primary key of another, enforcing that the referenced value actually exists. This is what implements the relationships in our practice database — employees.department_id is a foreign key referencing departments.id:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

With this in place, trying to insert an employee with a department_id that doesn't exist in departments would be rejected by the database.

NOT NULL

Forces a column to always have a value:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL
);

UNIQUE

Ensures no two rows share the same value in that column (unlike PRIMARY KEY, a table can have several UNIQUE columns, and — in most engines — UNIQUE columns can still contain one NULL):

CREATE TABLE employees (
  id INT PRIMARY KEY,
  email VARCHAR(150) UNIQUE
);

DEFAULT

Supplies an automatic value when none is given on insert:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_date DATE DEFAULT CURRENT_DATE,
  status VARCHAR(20) DEFAULT 'pending'
);

CHECK

Enforces a custom condition on every row:

CREATE TABLE products (
  id INT PRIMARY KEY,
  price INT CHECK (price >= 0)
);

Why this matters

Constraints move data-quality rules out of application code and into the database itself — so every application, script, or person touching the database is protected by the same guarantees, without having to remember to re-implement the checks everywhere.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;