SQL 11 🛢️ Primary Key and Foreign Key Constraints

Every table in a relational database needs a way to identify its rows. Every relationship between two tables needs a way to enforce that relationship. The primary key and the foreign key are the two constraints that make both possible. They are the most important constraints in the relational model, and they are the foundation of referential integrity.

The LFCA and SQL chapters in this series covered tables, rows, columns, and keys at a high level. This chapter goes deeper into the two constraints that define the structure of a relational schema. The primary key uniquely identifies each row. The foreign key connects the rows of one table to the rows of another. Together, they define the graph of the database and enforce the consistency that the relational model requires.

Key point: A primary key is a constraint. A foreign key is a constraint. Both are declared when the table is created and enforced by the database on every insert, update, and delete. The primary key prevents duplicate rows. The foreign key prevents orphaned references. The database rejects any operation that would violate either constraint. The constraints are the rules that keep the data consistent.

---

Why the two constraints matter

A table without a primary key is a bag of rows. Two rows can be identical. The application cannot reliably identify a row. A table with a primary key has a stable identity for every row. The application can reference the row by its key, and the key will not change.

The identity problem. A customers table without a primary key cannot distinguish two customers with the same name and email. A customers table with a primary key on customer_id can. The customer_id is the identity. It is unique and stable.

The relationship problem. An orders table needs to know which customer placed each order. A foreign key on customer_id connects the order to the customer. The database rejects any order that references a customer that does not exist. The relationship is enforced by the database, not by the application.

The cascade problem. A customer is deleted. What happens to their orders? The foreign key’s ON DELETE action determines the answer. CASCADE deletes the orders. RESTRICT rejects the delete. SET NULL sets the foreign key to null. The action is declared with the foreign key, and the database enforces it.

The performance problem. A primary key creates an index. The index makes lookups by key fast. A foreign key also benefits from an index on the referencing column. Without the index, a delete on the parent table requires a full scan of the child table to check the foreign key. The index is not automatically created by every database, but it should be added.

The trade-off. Both constraints add overhead. The primary key requires a unique index. The foreign key requires a check on every insert and update. The checks are fast, but they are not free. For a table with millions of rows and a high insert rate, the checks matter. The overhead is the price of consistency.

---

a. Primary Key Constraints

A primary key is a column or set of columns that uniquely identifies each row in a table. It has three properties: it is unique, it is not null, and it is stable. A table can have only one primary key .

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    email       VARCHAR(100) UNIQUE NOT NULL
);

The customer_id column is the primary key. The PRIMARY KEY keyword is shorthand for a NOT NULL constraint and a UNIQUE constraint. The database creates a unique index on the column to enforce the constraint.

The primary key can be declared at the table level, which is required for composite keys:

CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

The combination of order_id and product_id is unique. Neither column is unique on its own. The primary key is the pair.

A primary key can also be added to an existing table with ALTER TABLE:

ALTER TABLE customers
ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id);

The constraint is validated against the existing data. If any row has a null or duplicate value in the column, the statement fails. The data must be corrected first.

A primary key can be dropped with ALTER TABLE ... DROP CONSTRAINT. Dropping the primary key also drops the unique index that enforces it. The operation should be used with caution because other tables may have foreign keys that reference the primary key.

ALTER TABLE customers
DROP CONSTRAINT pk_customers;

---

b. Foreign Key Constraints

A foreign key is a column or set of columns in one table that references the primary key or a unique key of another table. It creates a relationship between the two tables and enforces referential integrity .

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL,
    order_date   DATE NOT NULL,
    total        DECIMAL(10, 2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

The customer_id column in orders is a foreign key that references the customer_id column in customers. The database rejects any insert into orders with a customer_id that does not exist in customers.

The foreign key can be declared inline with the column:

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL REFERENCES customers(customer_id),
    order_date   DATE NOT NULL
);

The inline form is shorter but does not allow naming the constraint. The table-level form is preferred for clarity and for the ability to name the constraint.

A foreign key can reference a unique key instead of the primary key. The referenced column must have a UNIQUE constraint:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    email       VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE employee_profiles (
    profile_id   INT PRIMARY KEY,
    employee_email VARCHAR(100) NOT NULL,
    bio          TEXT,
    FOREIGN KEY (employee_email) REFERENCES employees(email)
);

A foreign key can be self-referential. The manager_id column references the employee_id column in the same table:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    manager_id  INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

The self-referential foreign key models a hierarchy. An employee reports to another employee. The top-level employee has a null manager_id.

---

c. Referential Actions

The ON DELETE and ON UPDATE clauses specify what happens when the referenced row is deleted or updated. The actions are declared with the foreign key and enforced by the database.

NO ACTION is the default. The delete or update is rejected if there are referencing rows. The check is deferred until the end of the transaction if the constraint is deferrable.

RESTRICT is similar to NO ACTION but the check is immediate. The delete or update is rejected immediately if there are referencing rows.

CASCADE propagates the delete or update to the referencing rows. Deleting a customer deletes their orders. Updating a customer’s customer_id updates the customer_id in the orders.

SET NULL sets the foreign key to null when the referenced row is deleted or updated. The foreign key column must be nullable.

SET DEFAULT sets the foreign key to its default value when the referenced row is deleted or updated. The foreign key column must have a default.

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL,
    order_date   DATE NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

The ON DELETE CASCADE means that deleting a customer deletes their orders. The ON UPDATE CASCADE means that updating the customer’s customer_id updates the customer_id in the orders.

The choice of action depends on the business rule. A delete of a customer should probably not delete their order history. The RESTRICT action is the safer default. The CASCADE action is used when the child row has no meaning without the parent.

The table below summarizes the actions:

Action	On Delete	On Update
NO ACTION	Reject (deferred)	Reject (deferred)
RESTRICT	Reject (immediate)	Reject (immediate)
CASCADE	Delete children	Update children
SET NULL	Set FK to null	Set FK to null
SET DEFAULT	Set FK to default	Set FK to default

---

Complete Example Session

This session builds a small schema that demonstrates the primary key and the foreign key with the referential actions.

-- ============================================
-- PART 1: THE CUSTOMERS TABLE
-- ============================================

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    email       VARCHAR(100) UNIQUE NOT NULL
);

-- customer_id is the primary key.
-- email is a unique key.

-- ============================================
-- PART 2: THE ORDERS TABLE WITH A FOREIGN KEY
-- ============================================

CREATE TABLE orders (
    order_id     INT PRIMARY KEY,
    customer_id  INT NOT NULL,
    order_date   DATE NOT NULL,
    total        DECIMAL(10, 2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

-- customer_id references customers.
-- RESTRICT prevents deleting a customer with orders.
-- CASCADE propagates customer_id updates.

-- ============================================
-- PART 3: THE ORDER_ITEMS TABLE WITH A COMPOSITE KEY
-- ============================================

CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE
);

-- The primary key is the pair (order_id, product_id).
-- Deleting an order deletes its items.

-- ============================================
-- PART 4: INSERT DATA
-- ============================================

INSERT INTO customers VALUES (1, 'Alice', 'Johnson', 'alice@example.com');
INSERT INTO customers VALUES (2, 'Bob', 'Smith', 'bob@example.com');

INSERT INTO orders VALUES (1001, 1, '2026-10-01', 99.99);
INSERT INTO orders VALUES (1002, 2, '2026-10-01', 49.99);

INSERT INTO order_items VALUES (1001, 101, 1, 99.99);
INSERT INTO order_items VALUES (1002, 102, 2, 24.99);

-- ============================================
-- PART 5: TEST THE PRIMARY KEY
-- ============================================

INSERT INTO customers VALUES (1, 'Duplicate', 'Customer', 'dup@example.com');

-- Error: duplicate key value violates unique constraint "customers_pkey"
-- DETAIL: Key (customer_id)=(1) already exists.

-- The primary key prevents duplicate rows.

-- ============================================
-- PART 6: TEST THE FOREIGN KEY
-- ============================================

INSERT INTO orders VALUES (1003, 999, '2026-10-01', 0);

-- Error: insert or update on table "orders" violates foreign key constraint
-- "fk_orders_customer"
-- DETAIL: Key (customer_id)=(999) is not present in table "customers".

-- The foreign key prevents orphaned references.

-- ============================================
-- PART 7: TEST THE RESTRICT ACTION
-- ============================================

DELETE FROM customers WHERE customer_id = 1;

-- Error: update or delete on table "customers" violates foreign key constraint
-- "fk_orders_customer" on table "orders"
-- DETAIL: Key (customer_id)=(1) is still referenced from table "orders".

-- The RESTRICT action prevents deleting a customer with orders.

-- ============================================
-- PART 8: TEST THE CASCADE ACTION
-- ============================================

DELETE FROM orders WHERE order_id = 1002;

SELECT * FROM order_items WHERE order_id = 1002;

-- Output: empty
-- The order_items for order 1002 were deleted by the CASCADE action.

-- ============================================
-- PART 9: TEST THE ON UPDATE CASCADE
-- ============================================

UPDATE customers SET customer_id = 10 WHERE customer_id = 2;

SELECT * FROM orders WHERE customer_id = 10;

-- Output: the order that referenced customer 2 now references customer 10.

-- ============================================
-- PART 10: THE SCHEMA SUMMARY
-- ============================================

-- customers: customer_id (PK), first_name, last_name, email (UNIQUE)
-- orders: order_id (PK), customer_id (FK → customers), order_date, total
-- order_items: (order_id, product_id) (PK), quantity, unit_price

The ten parts cover the customers table, the orders table with a foreign key, the order_items table with a composite key, inserting data, testing the primary key, testing the foreign key, testing the restrict action, testing the cascade action, testing the on update cascade, and the schema summary.

---

Quick Reference

The Primary Key Properties

Property	Description
Unique	No two rows have the same key
Not null	The key cannot be null
Stable	The key does not change
One per table	A table can have only one primary key
Creates an index	The database indexes the primary key

The Foreign Key Properties

Property	Description
References	The primary key or unique key of another table
Enforces	Referential integrity
Multiple per table	A table can have many foreign keys
Nullable	A foreign key can be null unless declared NOT NULL
Self-referential	A foreign key can reference the same table

The Referential Actions

Action	On Delete	On Update
NO ACTION	Reject (deferred)	Reject (deferred)
RESTRICT	Reject (immediate)	Reject (immediate)
CASCADE	Delete children	Update children
SET NULL	Set FK to null	Set FK to null
SET DEFAULT	Set FK to default	Set FK to default

The ALTER TABLE Operations

Operation	Syntax
Add primary key	ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY (col)
Drop primary key	ALTER TABLE t DROP CONSTRAINT pk
Add foreign key	ALTER TABLE t ADD CONSTRAINT fk FOREIGN KEY (col) REFERENCES p(col)
Drop foreign key	ALTER TABLE t DROP CONSTRAINT fk

---

Best Practices

✅ Do This:

-- Declare the primary key explicitly
customer_id INT PRIMARY KEY                                       -- ✅

-- Name the foreign key constraint
CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) -- ✅

-- Use RESTRICT for the safe default
ON DELETE RESTRICT                                                -- ✅

-- Index the foreign key column for performance
CREATE INDEX idx_orders_customer ON orders(customer_id);          -- ✅

-- Use ON UPDATE CASCADE for stable keys
ON UPDATE CASCADE                                                 -- ✅

❌ Don’t Do This:

-- Don't use a business value as the primary key
email VARCHAR(100) PRIMARY KEY  -- email can change               -- ❌

-- Don't use CASCADE without understanding the consequences
ON DELETE CASCADE  -- deletes all referencing rows                -- ⚠️

-- Don't forget to index the foreign key
-- Without an index, deletes on the parent table are slow.     -- ❌

-- Don't allow null in a primary key column
customer_id INT PRIMARY KEY  -- cannot be null                    -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Duplicate key error	Primary key violation	Use a unique value
Foreign key violation	Referenced row missing	Insert the parent first
Cannot delete parent	Foreign key with RESTRICT	Use CASCADE or delete children
Slow deletes	No index on foreign key	Create the index
Orphaned rows	No foreign key constraint	Add the constraint

---

Real-World Examples

1. Primary Key

customer_id INT PRIMARY KEY

2. Composite Primary Key

PRIMARY KEY (order_id, product_id)

3. Foreign Key

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

4. Cascade Delete

ON DELETE CASCADE

5. Restrict Delete

ON DELETE RESTRICT

6. Set Null

ON DELETE SET NULL

7. Self-Referential

FOREIGN KEY (manager_id) REFERENCES employees(employee_id)

8. Add Constraint

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

9. Drop Constraint

ALTER TABLE orders DROP CONSTRAINT fk_customer

10. Index Foreign Key

CREATE INDEX idx_orders_customer ON orders(customer_id)

---

Visual

The Primary Key

┌──────────────────────────────────────────────┐
│  CUSTOMERS                                   │
│                                              │
│  customer_id │ first_name │ last_name        │
│  (PK)        │            │                  │
│  ────────────┼────────────┼────────────      │
│  1           │ Alice      │ Johnson          │
│  2           │ Bob        │ Smith            │
│  3           │ Carol      │ Williams         │
│                                              │
│  The primary key uniquely identifies each row│
│  It cannot be null or duplicated.            │
│                                              │
└──────────────────────────────────────────────┘

The Foreign Key

┌──────────────────────────────────────────────┐
│  CUSTOMERS (parent)                          │
│    customer_id (PK)                          │
│         ▲                                    │
│         │                                    │
│  ORDERS (child)                              │
│    order_id (PK)                             │
│    customer_id (FK)                          │
│                                              │
│  The foreign key references the primary key  │
│  of the parent table.                        │
│                                              │
└──────────────────────────────────────────────┘

The Referential Actions

┌──────────────────────────────────────────────┐
│  REFERENTIAL ACTIONS                         │
│                                              │
│  Delete parent:                              │
│    │                                         │
│    ├─ RESTRICT → reject                      │
│    ├─ CASCADE → delete children              │
│    ├─ SET NULL → FK becomes null             │
│    └─ SET DEFAULT → FK becomes default       │
│                                              │
│  The action is declared with the foreign key.│
│  The database enforces it.                   │
│                                              │
└──────────────────────────────────────────────┘

The Constraint Validation

┌──────────────────────────────────────────────┐
│  CONSTRAINT VALIDATION                       │
│                                              │
│  INSERT INTO orders (customer_id) VALUES (999)│
│       │                                      │
│       ▼                                      │
│  Database checks: customer 999 exists?       │
│    │                                         │
│    ├─ YES → insert succeeds                  │
│    └─ NO  → insert fails                     │
│                                              │
│  The check happens on every insert and update│
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
Primary key	Unique, not null, stable identifier
Primary key count	One per table
Composite key	Primary key with multiple columns
Foreign key	References another table’s primary key
Foreign key count	Multiple per table
Referential actions	NO ACTION, RESTRICT, CASCADE, SET NULL, SET DEFAULT
Default action	NO ACTION
Safe action	RESTRICT
Index	Should be created on foreign key columns

Key takeaways:

• A primary key uniquely identifies each row. It is unique, not null, and stable. A table can have only one primary key. The database creates a unique index to enforce the constraint .
• A foreign key references the primary key or a unique key of another table. It creates a relationship between the two tables and enforces referential integrity. The database rejects any insert or update that would create a dangling reference .
• A composite key is a primary key composed of multiple columns. The combination of the columns is unique. The PRIMARY KEY constraint is declared at the table level for composite keys .
• The referential actions determine what happens when the referenced row is deleted or updated. RESTRICT and NO ACTION reject the operation. CASCADE propagates it. SET NULL and SET DEFAULT modify the foreign key .
• The choice of referential action depends on the business rule. A delete of a customer should probably not delete their order history. The RESTRICT action is the safer default. The CASCADE action is used when the child row has no meaning without the parent.
• Foreign key columns should be indexed. Without an index, a delete on the parent table requires a full scan of the child table to check the foreign key. The index makes the check fast.
• The constraints are enforced by the database, not the application. The application cannot insert a row that violates the primary key or the foreign key. The database rejects the operation. The consistency of the data is guaranteed by the database.

Remember: The primary key is the identity of the row. The foreign key is the connection between rows. Together, they define the structure of the relational schema and enforce the consistency that the relational model requires. The primary key prevents duplicate rows. The foreign key prevents orphaned references. The referential actions determine what happens when the parent is deleted or updated. The constraints are the rules that keep the data consistent.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!