Keys, Foreign Keys & Constraints

#Introduction

This chapter covers the foundation of relational database design: keys, constraints, relationships and referential integrity.

You'll learn primary, foreign, candidate, alternate, composite, super, natural and surrogate keys; the UNIQUE, NOT NULL, CHECK and DEFAULT constraints; referential integrity; ON DELETE CASCADE, ON DELETE SET NULL, ON DELETE RESTRICT and ON UPDATE CASCADE; and enterprise design best practices — among the most important SQL and database-design interview topics for experienced Java backend developers.

#Why Do We Need Constraints?

Two employees share ID 101. Who does employee 101 represent — John or David? This causes duplicate data, incorrect updates, invalid relationships, data inconsistency and application bugs. Constraints prevent such problems at the database level.

#What Is a Constraint?

A constraint is a rule enforced by the database to maintain the accuracy, consistency and integrity of data:

• Employee ID must be unique.
• Employee name cannot be NULL.
• Salary cannot be negative.
• Email must not be duplicated.
• Every order must belong to an existing customer.
• Order status must contain only allowed values.

#Why Constraints Matter Even When Java Validates Data

A common interview question: if validation already exists in Java or Spring Boot, why do we still need database constraints?

Because the database may receive data from Spring Boot applications, batch jobs, SQL scripts, admin tools, data-migration tools, other microservices, ETL pipelines and manual database operations. Application validation alone cannot guarantee database integrity.

Application validation + database constraints = strong data integrity.

#Main Types of Constraints

#What Is a Key?

A key is one or more columns used to identify rows, enforce uniqueness, create relationships and access data efficiently. The important types are super, candidate, primary, alternate, composite, foreign, natural and surrogate keys.

#PRIMARY KEY

A primary key uniquely identifies every row in a table. It:

• must be unique;
• cannot contain NULL;
• can contain one or multiple columns;
• exists only once per table (one primary key constraint);
• is automatically indexed in MySQL — in InnoDB it is the clustered index that physically organizes the table.

CREATE TABLE employee (
    employee_id   INT PRIMARY KEY,
    employee_name VARCHAR(100),
    salary        DECIMAL(10, 2)
);

Valid, Duplicate and NULL Inserts

-- Valid: 101 doesn't exist yet
INSERT INTO employee (employee_id, employee_name, salary)
VALUES (101, 'John', 70000);

-- Rejected: duplicate primary key value 101
INSERT INTO employee (employee_id, employee_name, salary)
VALUES (101, 'David', 80000);

-- Rejected: a primary key cannot be NULL
INSERT INTO employee (employee_id, employee_name, salary)
VALUES (NULL, 'John', 70000);

ERROR 1062 (23000): Duplicate entry '101' for key 'employee.PRIMARY'

NULL means unknown, and an unknown value cannot uniquely identify a row.

Table-Level Syntax

A primary key can also be defined separately — both approaches are valid:

CREATE TABLE employee (
    employee_id   INT,
    employee_name VARCHAR(100),
    salary        DECIMAL(10, 2),
    PRIMARY KEY (employee_id)
);

AUTO_INCREMENT Primary Key

In MySQL, surrogate numeric identifiers are commonly generated with AUTO_INCREMENT:

CREATE TABLE employee (
    employee_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    salary        DECIMAL(10, 2)
);

INSERT INTO employee (employee_name, salary)
VALUES ('John', 70000);   -- MySQL generates employee_id automatically

#Composite Primary Key

Sometimes one column alone can't identify a row. Student enrollments:

Neither student_id nor course_id is unique alone, but student_id + course_id together uniquely identify an enrollment:

CREATE TABLE enrollment (
    student_id INT,
    course_id  INT,
    PRIMARY KEY (student_id, course_id)
);

Valid: 101 + Java, 101 + SQL, 102 + Java. Invalid: 101 + Java twice.

Composite keys are common in junction tables, many-to-many relationships and association tables — student + course, order + product, user + role, employee + project, account + permission.

#Candidate Key

A candidate key is any minimal column or combination of columns that can uniquely identify a row.

If all three values are guaranteed unique, employee_id, email and employee_code are all candidate keys — any one could be chosen as the primary key.

A candidate key must be unique + minimal: it must identify a row, and contain no unnecessary columns. If employee_id alone is unique, employee_id + employee_name is not a candidate key — employee_name is unnecessary. It's a super key.

#Primary, Alternate and Super Keys

The designer chooses one candidate key — say employee_id — as the primary key:

An alternate key is a candidate key not selected as the primary key. In SQL, alternate keys are usually enforced with UNIQUE.

A super key is any column or combination that uniquely identifies a row. If employee_id is unique, all of these are super keys: employee_id, employee_id + employee_name, employee_id + email, employee_id + employee_name + email. Only the minimal ones are candidate keys.

Key Hierarchy

#Natural vs Surrogate Keys

A natural key is a real-world business value that naturally identifies an entity — email address, passport number, employee code, ISBN, vehicle registration number:

CREATE TABLE employee (
    email         VARCHAR(255) PRIMARY KEY,   -- natural key
    employee_name VARCHAR(100)
);

A surrogate key is an artificial identifier created purely for database identity — employee_id, customer_id, order_id, product_id:

CREATE TABLE employee (
    employee_id   BIGINT AUTO_INCREMENT PRIMARY KEY,   -- surrogate key
    email         VARCHAR(255) UNIQUE,
    employee_name VARCHAR(100)
);

The Common Enterprise Pattern

Surrogate key → PRIMARY KEY; business identifiers → UNIQUE:

CREATE TABLE employee (
    employee_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    employee_code VARCHAR(50)  NOT NULL UNIQUE,
    email         VARCHAR(255) NOT NULL UNIQUE
);

Should We Always Avoid Natural Keys?

No. Natural keys can be appropriate when they are truly stable, small, guaranteed unique and never expected to change. But enterprise applications often prefer surrogate keys because business data changes — if email is the primary key and a user changes their email, the primary key changes and every related foreign key may need updating. A stable user_id avoids this.

#UNIQUE Constraint

UNIQUE prevents duplicate values:

CREATE TABLE employee (
    employee_id BIGINT PRIMARY KEY,
    email       VARCHAR(255) UNIQUE
);

john@example.com and david@example.com are valid; john@example.com twice is rejected.

Unlike a primary key, a table can have multiple UNIQUE constraints:

CREATE TABLE employee (
    employee_id   BIGINT PRIMARY KEY,
    employee_code VARCHAR(50) UNIQUE,
    email         VARCHAR(255) UNIQUE
);

PRIMARY KEY vs UNIQUE

Composite UNIQUE Constraint

Uniqueness can span several columns — this prevents assigning the same role to the same user twice:

CREATE TABLE user_role (
    user_id BIGINT,
    role_id BIGINT,
    UNIQUE (user_id, role_id)
);

#NOT NULL Constraint

NOT NULL makes a column mandatory:

CREATE TABLE employee (
    employee_id   BIGINT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL
);

-- Rejected
INSERT INTO employee (employee_id, employee_name)
VALUES (101, NULL);

Use NOT NULL when the business rule says this value must always exist — employee name, order date, product price, transaction amount, account status. Don't make every column NOT NULL automatically; the decision should reflect the business requirement.

#DEFAULT Constraint

A default value is used when no value is supplied:

CREATE TABLE employee (
    employee_id BIGINT PRIMARY KEY,
    status      VARCHAR(20) DEFAULT 'ACTIVE'
);

INSERT INTO employee (employee_id) VALUES (101);

DEFAULT Doesn't Replace an Explicit NULL

The default applies only when the column is omitted (or written as DEFAULT). Explicitly inserting NULL into a nullable column stores NULL. If the value must always exist, declare:

status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE'

#CHECK Constraint

A CHECK constraint validates that a value satisfies a condition:

CREATE TABLE employee (
    employee_id BIGINT PRIMARY KEY,
    salary      DECIMAL(10, 2),
    CONSTRAINT chk_employee_salary CHECK (salary >= 0)
);

-- Rejected
INSERT INTO employee (employee_id, salary) VALUES (101, -5000);

Multiple CHECKs and Allowed Values

CREATE TABLE product (
    product_id   BIGINT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    price        DECIMAL(10, 2) NOT NULL,
    stock        INT NOT NULL,
    CONSTRAINT chk_product_price CHECK (price > 0),
    CONSTRAINT chk_product_stock CHECK (stock >= 0)
);

CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    status   VARCHAR(20) NOT NULL,
    CONSTRAINT chk_order_status
        CHECK (status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'DELIVERED', 'CANCELLED'))
);

Invalid statuses are now rejected.

#FOREIGN KEY

A foreign key creates a relationship between tables.

orders.customer_id references customer.customer_id — orders.customer_id is the foreign key.

The referenced table (customer) is the parent; the table containing the foreign key (orders) is the child.

CREATE TABLE customer (
    customer_id   BIGINT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id    BIGINT PRIMARY KEY,
    customer_id BIGINT,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer (customer_id)
);

#Referential Integrity

Referential integrity ensures foreign-key relationships stay valid:

-- Customer 999 doesn't exist → rejected
INSERT INTO orders (order_id, customer_id) VALUES (1, 999);

Can a Foreign Key Be NULL?

Yes, if the column allows NULL — customer_id BIGINT NULL with a NULL value means no customer relationship assigned yet. If every order must belong to a customer, use customer_id BIGINT NOT NULL.

A foreign key enforces: if a non-NULL value exists, it must reference a valid parent.

Foreign-Key Requirements in MySQL

• Parent and child columns must use compatible data types — avoid mismatches such as parent BIGINT UNSIGNED vs child BIGINT.
• The referenced parent columns must be indexed (usually the primary key or a unique key).
• The storage engine must support foreign keys (InnoDB does; MyISAM doesn't).
• The referenced value must exist, unless the child foreign key is NULL.

#What Happens When a Parent Row Is Deleted?

Customer 101 has 10 orders, and someone runs DELETE FROM customer WHERE customer_id = 101;. What happens to the orders depends on the referential action: CASCADE, SET NULL, RESTRICT or NO ACTION.

ON DELETE CASCADE

Automatically deletes the related child rows when the parent is deleted:

CREATE TABLE orders (
    order_id    BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer (customer_id)
        ON DELETE CASCADE
);

DELETE FROM customer WHERE customer_id = 101;   -- customer 101's orders are deleted too

Appropriate when the child has no meaning without the parent — order → order items, blog post → comments, temporary parent → dependent temporary records.

ON DELETE SET NULL

Keeps the child row but removes the relationship:

CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customer (customer_id)
    ON DELETE SET NULL

Delete the customer → keep the order → customer_id = NULL.

Useful when, for example, an employee leaves but their historical tasks are kept (assigned_employee_id = NULL), or an optional category is deleted but its products remain (category_id = NULL).

ON DELETE RESTRICT

Prevents deleting the parent while related child rows exist:

CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customer (customer_id)
    ON DELETE RESTRICT

Customer has orders → attempt to delete the customer → the database rejects the delete. Use it when deleting the parent would break business rules — a customer with financial transactions, an account with transactions, a product referenced by historical records, a department with active employees. It forces the application to handle dependent data explicitly.

NO ACTION vs RESTRICT

In the SQL standard, NO ACTION and RESTRICT can differ in when enforcement happens (NO ACTION can be deferred). In MySQL's InnoDB they behave the same — both block parent changes while dependent child rows exist. If you specify no ON DELETE / ON UPDATE clause at all, MySQL uses NO ACTION.

ON UPDATE CASCADE

Automatically updates child foreign-key values when the referenced parent key changes:

CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customer (customer_id)
    ON UPDATE CASCADE

Customer ID 101 → 500 updates every child foreign key from 101 to 500 as well. Stable surrogate primary keys (AUTO_INCREMENT IDs) normally never change, so ON UPDATE CASCADE is mostly useful when relationships reference mutable business keys.

Referential Action Comparison

Decision Guide

#Complete Customer and Orders Example

CREATE TABLE customer (
    customer_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email         VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    order_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date  DATETIME NOT NULL,
    amount      DECIMAL(12, 2) NOT NULL,
    CONSTRAINT chk_orders_amount CHECK (amount >= 0),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer (customer_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

#Naming Constraints

Instead of letting the database generate names, give constraints meaningful names — CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id).

Meaningful names make debugging easier, migration scripts clearer and production errors easier to understand.

#Real-World Designs

Banking

A transaction should never reference a nonexistent account — foreign keys enforce that.

E-commerce

University — a Many-to-Many Junction Table

One student takes many courses and one course has many students — many-to-many. Relational databases resolve it with a junction table holding foreign keys to both parents:

CREATE TABLE enrollment (
    student_id      BIGINT,
    course_id       BIGINT,
    enrollment_date DATE NOT NULL,
    PRIMARY KEY (student_id, course_id),
    CONSTRAINT fk_enrollment_student
        FOREIGN KEY (student_id) REFERENCES student (student_id),
    CONSTRAINT fk_enrollment_course
        FOREIGN KEY (course_id)  REFERENCES course (course_id)
);

HRMS

CREATE TABLE department (
    department_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employee (
    employee_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id BIGINT NOT NULL,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES department (department_id)
        ON DELETE RESTRICT
);

A department can't be deleted while employees reference it.

#Mini Project — E-commerce Schema

Four tables: customer, product, orders and order_item.

CREATE TABLE customer (
    customer_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email         VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE product (
    product_id   BIGINT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    price        DECIMAL(10, 2) NOT NULL,
    CONSTRAINT chk_product_price CHECK (price > 0)
);

CREATE TABLE orders (
    order_id    BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status      VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    CONSTRAINT chk_order_status
        CHECK (status IN ('PENDING', 'CONFIRMED', 'SHIPPED', 'DELIVERED', 'CANCELLED')),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer (customer_id)
        ON DELETE RESTRICT
);

CREATE TABLE order_item (
    order_id   BIGINT,
    product_id BIGINT,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT chk_order_item_quantity CHECK (quantity > 0),
    CONSTRAINT fk_order_item_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_order_item_product
        FOREIGN KEY (product_id)
        REFERENCES product (product_id)
        ON DELETE RESTRICT
);

Why Different Delete Rules?

• orders → order_item: ON DELETE CASCADE — an order item has no meaning without its order.
• product → order_item: ON DELETE RESTRICT — deleting a product must not erase historical order items.

#Spring Boot and JPA Perspective

A typical JPA entity uses a surrogate primary key and a @ManyToOne relationship:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@ManyToOne
@JoinColumn(name = "customer_id")
private Customer customer;

A JPA relationship does not automatically guarantee a database constraint — the actual schema should still enforce the required foreign keys (in production, managed by migrations such as Flyway or Liquibase rather than ddl-auto).

JPA Cascade vs Database ON DELETE CASCADE

An important interview distinction:

They are different mechanisms — don't confuse them.

#Soft Delete vs Physical Delete

Many enterprise applications don't physically delete important records:

UPDATE customer SET status = 'INACTIVE' WHERE customer_id = 101;
-- or
UPDATE customer SET deleted = TRUE WHERE customer_id = 101;

This soft delete supports auditing, historical reporting, recovery and compliance. Foreign-key design should consider whether records are physically deleted at all.

#Constraint Design Mental Model

Before creating a column, ask:

For every foreign key, ask: can the child exist without the parent? Should the child disappear when the parent is deleted? Should parent deletion be blocked? Must historical data be preserved? Then choose CASCADE, SET NULL, RESTRICT or soft delete based on the business requirement.

#Practice Problems

Easy

1. Create an employee table with a primary key.
2. Create a UNIQUE email column.
3. Create a NOT NULL name column.
4. Create a DEFAULT status column.
5. Create a CHECK constraint for salary.
6. Create an AUTO_INCREMENT surrogate key.

Medium

1. Create customer and orders tables with a foreign key.
2. Create a composite primary key.
3. Implement ON DELETE CASCADE.
4. Implement ON DELETE SET NULL.
5. Implement ON DELETE RESTRICT.
6. Implement ON UPDATE CASCADE.
7. Design a student–course enrollment table.
8. Create a composite UNIQUE constraint.

Interview level

1. Explain candidate key vs super key.
2. Explain primary key vs foreign key.
3. Explain primary key vs unique key.
4. Explain natural key vs surrogate key.
5. Explain CASCADE vs RESTRICT vs SET NULL.
6. Explain referential integrity.
7. Explain JPA cascade vs database cascade.
8. Design an e-commerce database with proper constraints.
9. Discuss when natural keys should be avoided.
10. Explain why application validation doesn't replace database constraints.
11. Design foreign-key behavior for a financial system.

#Key Takeaways

• A primary key uniquely identifies every row; a table has one primary key constraint, which may span multiple columns.
• A candidate key is a minimal unique identifier; the chosen one becomes the primary key and the rest are alternate keys.
• Every candidate key is a super key, but not every super key is a candidate key.
• Composite keys combine columns; natural keys have business meaning; surrogate keys are artificial identifiers.
• UNIQUE protects additional business identifiers, NOT NULL makes values mandatory, DEFAULT fills omitted values and CHECK enforces domain rules.
• Foreign keys create and protect relationships; referential integrity prevents invalid references.
• CASCADE deletes dependent children, SET NULL keeps them without the relationship, RESTRICT blocks deletion — choose per relationship by business rules.
• Database constraints complement application-level validation.