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.