SQL Constraints, Primary Keys and Foreign Keys

SQL constraints are rules attached to columns or tables that stop bad data from being saved. They are your last line of defence: even if application code has a bug, the database will refuse invalid rows.

Types of Constraints

  • PRIMARY KEY: uniquely identifies each row; cannot be NULL
  • FOREIGN KEY: value must exist in another table's key
  • NOT NULL: a value is required
  • UNIQUE: no two rows may have the same value
  • CHECK: value must satisfy a condition
  • DEFAULT: value used when none is provided

Defining Constraints

This table uses several constraints. The insert leaves Status out, so the DEFAULT is used.

CREATE TABLE Staff (
  StaffID  INTEGER PRIMARY KEY,
  Email    TEXT NOT NULL UNIQUE,
  Salary   REAL CHECK (Salary > 0),
  Status   TEXT DEFAULT 'active'
);

INSERT INTO Staff (Email, Salary) VALUES ('ayesha@example.com', 2500);
SELECT * FROM Staff;
StaffID | Email              | Salary | Status
--------+--------------------+--------+-------
1       | ayesha@example.com | 2500   | active
(1 row)

A Constraint Violation

Try inserting a negative salary: the database rejects the row with an error. Running this example shows a "CHECK constraint failed" message.

CREATE TABLE Staff (
  StaffID INTEGER PRIMARY KEY,
  Salary  REAL CHECK (Salary > 0)
);

INSERT INTO Staff (Salary) VALUES (-100);
Error: CHECK constraint failed: Salary > 0

Foreign Keys

A foreign key links a column to the primary key of another table, so an order cannot point to a customer that does not exist. In SQLite you must enable checking with PRAGMA foreign_keys = ON; MySQL (InnoDB), PostgreSQL and SQL Server enforce them automatically.

PRAGMA foreign_keys = ON;

CREATE TABLE Invoices (
  InvoiceID  INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL REFERENCES Customers(CustomerID),
  Amount     REAL
);

INSERT INTO Invoices (CustomerID, Amount) VALUES (3, 99.5);
SELECT i.InvoiceID, c.CustomerName, i.Amount
FROM Invoices i JOIN Customers c ON c.CustomerID = i.CustomerID;
InvoiceID | CustomerName | Amount
----------+--------------+-------
1         | Cedar Market | 99.5
(1 row)

ON DELETE Actions

A foreign key can say what happens when the parent row is deleted: ON DELETE CASCADE deletes child rows too, ON DELETE SET NULL clears the reference, and the default (RESTRICT / NO ACTION) blocks the delete.

Frequently asked questions

What is the difference between a primary key and a unique key?

A table has only one primary key and it cannot be NULL. It can have many UNIQUE constraints, and UNIQUE columns may allow NULL.

Can a primary key have multiple columns?

Yes, a composite primary key such as PRIMARY KEY (OrderID, ProductID) makes the combination unique.

Should I use foreign keys?

Usually yes. They guarantee relationships stay valid. Some very high-scale systems skip them for speed, but they then need extra application checks.