PostgreSQL Lesson 11 – Constraints & Keys | Dataplexa

PostgreSQL

I. Foundations
1. Introduction to Databases 2. SQL vs NoSQL Databases 3. What is PostgreSQL 4. PostgreSQL Features & Advantages 5. Installing PostgreSQL 6. PostgreSQL Architecture 7. PostgreSQL Tools (psql & pgAdmin) 8. Creating Databases & Schemas 9. PostgreSQL Data Types 10. Creating Tables 11. Constraints & Keys 12. NOT NULL, DEFAULT & CHECK
II. Core SQL
13. Altering Tables 14. Drop & Truncate Tables 15. Identity Columns & Sequences 16. INSERT Data 17. SELECT Queries 18. WHERE & Filtering 19. ORDER BY, LIMIT & OFFSET 20. UPDATE Data 21. DELETE Data 22. RETURNING Clause 23. Aggregate Functions 24. GROUP BY 25. HAVING Clause 26. NULL Handling
III. Advanced SQL & Performance
27. Table Relationships 28. INNER JOIN 29. LEFT, RIGHT & FULL JOIN 30. Subqueries 31. Views 32. Materialized Views 33. String Functions 34. Date & Time Functions 35. CASE Expressions 36. Indexes 37. Transactions & ACID 38. Locks & Concurrency
IV. Enterprise & Cloud
39. Query Optimization 40. Stored Procedures & Functions 41. Triggers 42. JSON & JSONB 43. Users, Roles & Security 44. Backup & Restore 45. PostgreSQL on AWS (RDS)

Constraints & Keys

A database without constraints is just a dumping ground for data. Constraints are the rules you build into your tables that PostgreSQL enforces automatically — they prevent bad data from ever getting in, keep relationships between tables intact, and save you from writing defensive code in your application to check every value manually. This lesson covers every major constraint type with real examples from the Dataplexa Store dataset.

PRIMARY KEY

A primary key uniquely identifies every row in a table. No two rows can have the same primary key value, and the primary key column can never be NULL. Every table should have a primary key — without one you have no reliable way to refer to a specific row.

-- Method 1: inline primary key — most common for single column
CREATE TABLE customers (
  id         SERIAL       PRIMARY KEY,  -- unique, not null, auto-increments
  first_name VARCHAR(50)  NOT NULL,
  email      VARCHAR(100) NOT NULL
);

-- Method 2: table-level primary key — required for composite keys
CREATE TABLE order_items (
  order_id    INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  quantity    INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)  -- combination of both columns must be unique
);

-- Trying to insert a duplicate primary key — PostgreSQL rejects it
INSERT INTO customers (id, first_name, email) VALUES (1, 'Alice', 'alice@example.com');
INSERT INTO customers (id, first_name, email) VALUES (1, 'Bob',   'bob@example.com');  -- fails
INSERT 0 1
ERROR: duplicate key value violates unique constraint "customers_pkey"
DETAIL: Key (id)=(1) already exists.

A composite primary key uses two or more columns together as the unique identifier. In the order_items example, the same product can appear in different orders and the same order can have multiple products — but the exact combination of order_id and product_id can only appear once.

UNIQUE Constraint

The UNIQUE constraint ensures that no two rows have the same value in a column — just like a primary key, but it allows NULL values (multiple NULLs are permitted since NULL is considered not equal to anything, including itself). Use UNIQUE on columns that must be distinct but are not the primary identifier — email addresses, usernames, phone numbers.

CREATE TABLE employees (
  id       SERIAL       PRIMARY KEY,
  email    VARCHAR(100) NOT NULL UNIQUE,   -- inline unique constraint
  username VARCHAR(50)  UNIQUE             -- also unique, but nullable
);

-- You can also name the constraint for easier error messages
CREATE TABLE products (
  id    SERIAL       PRIMARY KEY,
  sku   VARCHAR(20)  NOT NULL,
  CONSTRAINT products_sku_unique UNIQUE (sku)  -- named constraint
);

-- Multi-column unique — the combination must be unique
CREATE TABLE employee_departments (
  employee_id   INTEGER NOT NULL,
  department_id INTEGER NOT NULL,
  UNIQUE (employee_id, department_id)  -- same employee cannot be in same dept twice
);
CREATE TABLE
CREATE TABLE
CREATE TABLE

NOT NULL Constraint

NOT NULL means a column must always have a value — you cannot insert or update a row and leave that column empty. This is one of the simplest and most important constraints. If a piece of information is essential to your table's purpose, mark it NOT NULL.

CREATE TABLE orders (
  id           SERIAL        PRIMARY KEY,
  customer_id  INTEGER       NOT NULL,      -- every order must belong to a customer
  order_date   DATE          NOT NULL,      -- every order must have a date
  status       VARCHAR(20)   NOT NULL DEFAULT 'pending',  -- required, but has a default
  notes        TEXT                         -- optional — can be NULL
);

-- Attempting to insert without a required field
INSERT INTO orders (order_date, status) VALUES ('2024-06-01', 'pending');  -- fails
ERROR: null value in column "customer_id" of relation "orders"
violates not-null constraint
DETAIL: Failing row contains (1, null, 2024-06-01, pending, null).

FOREIGN KEY Constraint

A foreign key creates a link between two tables and enforces referential integrity — it guarantees that a value in one table actually exists in the table it references. If an order references customer_id 99 but customer 99 does not exist in the customers table, PostgreSQL rejects the insert. This prevents orphaned records — orders without customers, order items without orders.

CREATE TABLE orders (
  id           SERIAL  PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total_amount NUMERIC(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

-- Named foreign key with ON DELETE behaviour
CREATE TABLE order_items (
  id          SERIAL  PRIMARY KEY,
  order_id    INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  quantity    INTEGER NOT NULL DEFAULT 1,
  CONSTRAINT fk_order    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
  CONSTRAINT fk_product  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

-- Try inserting an order for a customer that does not exist
INSERT INTO orders (customer_id, total_amount) VALUES (9999, 250.00);  -- fails
ERROR: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL: Key (customer_id)=(9999) is not present in table "customers".

The ON DELETE option controls what happens to child rows when a parent row is deleted. CASCADE automatically deletes all order_items when their parent order is deleted — keeping data clean. RESTRICT prevents deleting a product that still has order_items referencing it — protecting important data from accidental deletion. SET NULL sets the foreign key column to NULL when the parent is deleted. SET DEFAULT sets it to the column's default value.

DEFAULT Constraint

DEFAULT sets a fallback value for a column when no value is provided during an INSERT. It keeps your INSERT statements shorter and ensures sensible starting values without relying on the application to supply them.

CREATE TABLE products (
  id          SERIAL         PRIMARY KEY,
  name        VARCHAR(100)   NOT NULL,
  price       NUMERIC(10,2)  NOT NULL,
  stock_qty   INTEGER        DEFAULT 0,           -- starts at zero stock
  is_active   BOOLEAN        DEFAULT TRUE,        -- listed by default
  created_at  TIMESTAMPTZ    DEFAULT NOW(),        -- auto timestamp
  category    VARCHAR(50)    DEFAULT 'Uncategorised'
);

-- Insert without specifying optional columns — defaults kick in
INSERT INTO products (name, price) VALUES ('Standing Desk', 349.99);

SELECT name, stock_qty, is_active, category FROM products WHERE name = 'Standing Desk';
INSERT 0 1

    name       | stock_qty | is_active |   category
---------------+-----------+-----------+---------------
Standing Desk |         0 | t         | Uncategorised

CHECK Constraint

A CHECK constraint lets you write a custom rule that every row must satisfy. The rule is any boolean expression — PostgreSQL evaluates it on every INSERT and UPDATE and rejects rows where the expression is false. This is how you enforce business rules at the database level — not just in your application code.

CREATE TABLE products (
  id        SERIAL        PRIMARY KEY,
  name      VARCHAR(100)  NOT NULL,
  price     NUMERIC(10,2) NOT NULL  CHECK (price > 0),               -- price must be positive
  stock_qty INTEGER       DEFAULT 0 CHECK (stock_qty >= 0),          -- no negative stock
  rating    NUMERIC(2,1)            CHECK (rating BETWEEN 1 AND 5),  -- rating 1.0 to 5.0
  category  VARCHAR(50)             CHECK (category IN ('Electronics','Office','Furniture'))
);

-- Named check constraints for clearer error messages
CREATE TABLE employees (
  id      SERIAL        PRIMARY KEY,
  salary  NUMERIC(10,2) NOT NULL,
  CONSTRAINT salary_positive CHECK (salary > 0),
  CONSTRAINT salary_reasonable CHECK (salary <= 500000)
);

-- Try inserting a negative price — fails
INSERT INTO products (name, price) VALUES ('Broken Item', -50.00);
ERROR: new row for relation "products" violates check constraint "products_price_check"
DETAIL: Failing row contains (1, Broken Item, -50.00, 0, null, null).

Adding and Removing Constraints on Existing Tables

You do not have to define constraints only when creating a table. You can add or remove them later using ALTER TABLE. This is useful when your requirements change or when you are adding constraints to an existing database.

-- Add a NOT NULL constraint to an existing column
ALTER TABLE customers ALTER COLUMN phone SET NOT NULL;

-- Add a UNIQUE constraint to an existing column
ALTER TABLE customers ADD CONSTRAINT customers_phone_unique UNIQUE (phone);

-- Add a CHECK constraint to an existing column
ALTER TABLE products ADD CONSTRAINT products_price_positive CHECK (price > 0);

-- Add a FOREIGN KEY to an existing table
ALTER TABLE orders ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Remove a constraint by name
ALTER TABLE products DROP CONSTRAINT products_price_positive;

-- Remove NOT NULL from a column
ALTER TABLE customers ALTER COLUMN phone DROP NOT NULL;
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE

Viewing Constraints on a Table

-- See all constraints on a table using \d in psql
\d orders

-- Or query the information schema for full details
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'orders';
 constraint_name             | constraint_type
--------------------------------+-----------------
orders_pkey                   | PRIMARY KEY
orders_customer_id_fkey       | FOREIGN KEY
orders_status_check           | CHECK

Constraints Quick Reference

Constraint What It Enforces Common Use
PRIMARY KEY Unique + not null per row id column on every table
UNIQUE No duplicate values (nulls allowed) email, username, SKU
NOT NULL Column must always have a value Any required field
FOREIGN KEY Value must exist in referenced table customer_id, product_id
DEFAULT Fallback value when none is provided status, stock, timestamps
font-family:monospace;">CHECK Custom boolean rule per row price > 0, rating BETWEEN 1 AND 5

🧪 Practice Questions

Answer based on what you learned in this lesson.

1. Which constraint guarantees that a column is both unique and never NULL?



2. Which ON DELETE option automatically deletes child rows when the parent row is deleted?



3. Which constraint lets you write a custom rule like price > 0 that every row must satisfy?



4. Which constraint ensures no two rows have the same value in a column but still allows NULL values?



5. Which SQL command lets you add or remove a constraint on an existing table?



🎯 Quiz — Test Your Understanding

Q1. What does a FOREIGN KEY constraint on orders.customer_id actually do at runtime?






Q2. You want to ensure the same product cannot appear twice in the same order. Which constraint achieves this?






Q3. Which ON DELETE option prevents deleting a product that still has order items referencing it?






Q4. How does a UNIQUE constraint handle NULL values differently from a PRIMARY KEY?






Q5. Why is it a good practice to name your constraints using the CONSTRAINT keyword?






Next up: NOT NULL, DEFAULT, and CHECK constraints explored in even deeper detail with real data.

← Previous Course Index Next →