Back to SQL Basics: SELECT, WHERE, ORDER BY
Q8Question 8 of 10

How do primary keys, foreign keys, and constraints enforce data int

Answer: Constraints are rules enforced by the database engine that guarantee data integrity: primary keys ensure uniqueness, foreign keys prevent orphan records, unique constraints prevent duplicates, and check constraints validate business rules at the data layer.

Key Concepts
  • A primary key uniquely identifies every row in a table and cannot be NULL.
  • A foreign key references the primary key of another table and enforces referential integrity by preventing orphan records.
  • A unique constraint ensures no two rows have the same value in a column but allows one NULL.
  • A check constraint validates that column values satisfy a boolean condition before allowing an insert or update.
  • A NOT NULL constraint prevents a column from storing NULL values.
  • Constraint violations raise database errors that your application must catch and translate into user-friendly messages.
  • Cascading actions (ON DELETE CASCADE, ON UPDATE CASCADE) automatically propagate changes from the parent table to child tables.

Definition

Constraints are rules enforced by the database engine that guarantee data integrity: primary keys ensure uniqueness, foreign keys prevent orphan records, unique constraints prevent duplicates, and check constraints validate business rules at the data layer.
  • Primary key is a column (or set of columns) that uniquely identifies each row. It is automatically NOT NULL and UNIQUE.
  • Foreign key references the primary key of another table. The database rejects any insert or update that would create an orphan record.
  • Unique constraint ensures no duplicate values in a column. Unlike a primary key, a table can have multiple unique constraints.
  • Check constraint enforces a boolean condition on column values (for example, balance >= 0 or account_type IN ('SAVINGS', 'CURRENT')).
  • NOT NULL constraint prevents a column from accepting NULL. Every mandatory field in a banking schema should have this constraint.

Think of constraints as the rules enforced by the vault at National Bank of India. The primary key is like the unique vault box number. No two boxes can have the same number (uniqueness), and every box must have a number (NOT NULL). A foreign key is like requiring that every vault box must belong to a registered customer. If the customer does not exist, you cannot assign the box. A check constraint is like the rule that a vault box cannot hold a negative number of items. The database enforces these rules automatically, just like the vault's locking mechanism.

Understanding the Concepts

What is a primary key?

A primary key uniquely identifies every row in a table. It combines two constraints: UNIQUE (no duplicates) and NOT NULL (no missing values). Every table should have exactly one primary key.

A primary key can be a single column or a composite of multiple columns.

SQL
-- Single-column primary key
CREATE TABLE customers (
    customer_id   BIGSERIAL     PRIMARY KEY,
    full_name     VARCHAR(100)  NOT NULL,
    email         VARCHAR(150)  NOT NULL
);

-- Composite primary key (for a junction/bridge table)
CREATE TABLE account_holders (
    account_id    VARCHAR(10)   NOT NULL REFERENCES accounts(account_id),
    customer_id   BIGINT        NOT NULL REFERENCES customers(customer_id),
    role          VARCHAR(20)   NOT NULL DEFAULT 'PRIMARY',
    PRIMARY KEY (account_id, customer_id)
);
TEXT
-- Attempting to insert a duplicate primary key:
INSERT INTO customers (customer_id, full_name, email)
VALUES (1, 'Rajesh Kumar', 'rajesh@email.com');

INSERT INTO customers (customer_id, full_name, email)
VALUES (1, 'Priya Sharma', 'priya@email.com');

ERROR: duplicate key value violates unique constraint "customers_pkey"
Detail: Key (customer_id)=(1) already exists.

What is a foreign key?

A foreign key is a column that references the primary key of another table. It enforces referential integrity: you cannot insert a row that refers to a parent row that does not exist, and you cannot delete a parent row that has child rows (unless cascading is configured).

SQL
CREATE TABLE accounts (
    account_id    VARCHAR(10)    PRIMARY KEY,
    customer_id   BIGINT         NOT NULL
        REFERENCES customers(customer_id),
    balance       NUMERIC(15, 2) NOT NULL DEFAULT 0.00
);

CREATE TABLE transactions (
    txn_id        BIGSERIAL      PRIMARY KEY,
    account_id    VARCHAR(10)    NOT NULL
        REFERENCES accounts(account_id),
    txn_type      VARCHAR(10)    NOT NULL,
    amount        NUMERIC(15, 2) NOT NULL
);
TEXT
-- Insert a transaction for a non-existent account:
INSERT INTO transactions (account_id, txn_type, amount)
VALUES ('INVALID999', 'CREDIT', 5000.00);

ERROR: insert or update on table "transactions" violates
foreign key constraint "transactions_account_id_fkey"
Detail: Key (account_id)=(INVALID999) is not present in table "accounts".
TEXT
-- Try to delete a customer who still has accounts:
DELETE FROM customers WHERE customer_id = 1;

ERROR: update or delete on table "customers" violates
foreign key constraint "accounts_customer_id_fkey" on table "accounts"
Detail: Key (customer_id)=(1) is still referenced from table "accounts".

What are cascading actions?

Cascading actions define what happens to child rows when a parent row is updated or deleted.

ActionBehavior
RESTRICT (default)Reject the delete/update if child rows exist.
CASCADEDelete/update all child rows automatically.
SET NULLSet the foreign key column to NULL in child rows.
SET DEFAULTSet the foreign key column to its default value.
NO ACTIONSame as RESTRICT but checked at the end of the transaction.
SQL
-- CASCADE: deleting a customer deletes all their accounts
CREATE TABLE accounts_cascade (
    account_id    VARCHAR(10) PRIMARY KEY,
    customer_id   BIGINT      NOT NULL
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    balance       NUMERIC(15, 2) NOT NULL
);

-- SET NULL: closing a branch sets branch_id to NULL on accounts
CREATE TABLE accounts_setnull (
    account_id    VARCHAR(10) PRIMARY KEY,
    branch_id     INT
        REFERENCES branches(branch_id)
        ON DELETE SET NULL,
    balance       NUMERIC(15, 2) NOT NULL
);
TEXT
-- With CASCADE:
DELETE FROM customers WHERE customer_id = 1;
-- All accounts for customer 1 are automatically deleted.
-- All transactions for those accounts are also deleted
-- (if transactions FK has CASCADE too).

-- With SET NULL:
DELETE FROM branches WHERE branch_id = 5;
-- All accounts at branch 5 now have branch_id = NULL.

In banking systems, use CASCADE with extreme caution. Deleting a customer should never silently delete their transaction history. Prefer RESTRICT and handle deletions explicitly in your application code.

What is a unique constraint?

A unique constraint prevents duplicate values in a column. Unlike a primary key, a table can have many unique constraints, and a unique column allows one NULL value (in most databases).

SQL
CREATE TABLE customers (
    customer_id   BIGSERIAL     PRIMARY KEY,
    full_name     VARCHAR(100)  NOT NULL,
    email         VARCHAR(150)  UNIQUE NOT NULL,
    pan_number    CHAR(10)      UNIQUE NOT NULL,
    phone         VARCHAR(15)   UNIQUE  -- allows one NULL
);
TEXT
-- Insert two customers with the same PAN number:
INSERT INTO customers (full_name, email, pan_number)
VALUES ('Rajesh Kumar', 'rajesh@email.com', 'ABCPK1234A');

INSERT INTO customers (full_name, email, pan_number)
VALUES ('Priya Sharma', 'priya@email.com', 'ABCPK1234A');

ERROR: duplicate key value violates unique constraint
"customers_pan_number_key"
Detail: Key (pan_number)=(ABCPK1234A) already exists.

What is a check constraint?

A check constraint enforces a boolean condition on column values. The database rejects any insert or update that violates the condition.

SQL
CREATE TABLE accounts (
    account_id    VARCHAR(10)    PRIMARY KEY,
    customer_id   BIGINT         NOT NULL REFERENCES customers(customer_id),
    account_type  VARCHAR(10)    NOT NULL
        CHECK (account_type IN ('SAVINGS', 'CURRENT')),
    balance       NUMERIC(15, 2) NOT NULL
        CHECK (balance >= 0),
    interest_rate NUMERIC(5, 2)
        CHECK (interest_rate >= 0 AND interest_rate <= 100),
    status        VARCHAR(10)    NOT NULL DEFAULT 'ACTIVE'
        CHECK (status IN ('ACTIVE', 'FROZEN', 'CLOSED'))
);
TEXT
-- Insert an account with negative balance:
INSERT INTO accounts (account_id, customer_id, account_type, balance)
VALUES ('SAV2003', 1, 'SAVINGS', -500.00);

ERROR: new row for relation "accounts" violates check constraint
"accounts_balance_check"
Detail: Failing row contains (SAV2003, 1, SAVINGS, -500.00, ...).
TEXT
-- Insert an account with invalid type:
INSERT INTO accounts (account_id, customer_id, account_type, balance)
VALUES ('SAV2003', 1, 'FIXED', 10000.00);

ERROR: new row for relation "accounts" violates check constraint
"accounts_account_type_check"
Detail: Failing row contains (SAV2003, 1, FIXED, 10000.00, ...).

What is a NOT NULL constraint?

NOT NULL prevents a column from accepting NULL values. Every mandatory field in a banking schema should have this constraint.

SQL
CREATE TABLE transactions (
    txn_id       BIGSERIAL      PRIMARY KEY,
    account_id   VARCHAR(10)    NOT NULL REFERENCES accounts(account_id),
    txn_type     VARCHAR(10)    NOT NULL,
    amount       NUMERIC(15, 2) NOT NULL,
    description  VARCHAR(200)   -- nullable, optional
);
TEXT
-- Insert a transaction without amount:
INSERT INTO transactions (account_id, txn_type)
VALUES ('SAV2001', 'CREDIT');

ERROR: null value in column "amount" of relation "transactions"
violates not-null constraint
Detail: Failing row contains (1, SAV2001, CREDIT, null, null).

How do you handle constraint violations in Java?

When a constraint is violated, the database throws an exception. In Spring, @Repository enables automatic exception translation from JDBC-specific exceptions to Spring's DataAccessException hierarchy.

JAVA
package com.bank.service;

import com.bank.exception.DuplicateAccountException;
import com.bank.model.Account;
import com.bank.repository.AccountRepository;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.stereotype.Service;
import java.math.BigDecimal;

@Service
public class AccountService {

    private final AccountRepository accountRepository;

    public AccountService(AccountRepository accountRepository) {
        this.accountRepository = accountRepository;
    }

    public Account createAccount(String accountId,
            long customerId, BigDecimal initialDeposit) {
        try {
            Account account = new Account();
            account.setAccountId(accountId);
            account.setCustomerId(customerId);
            account.setBalance(initialDeposit);
            account.setAccountType("SAVINGS");
            return accountRepository.save(account);
        } catch (DataIntegrityViolationException ex) {
            if (ex.getMessage() != null
                && ex.getMessage().contains("accounts_pkey")) {
                throw new DuplicateAccountException(
                    "Account already exists: " + accountId);
            }
            throw ex;
        }
    }
}
TEXT
// Calling createAccount("SAV2001", 1, 25000) when SAV2001 exists:
DuplicateAccountException: Account already exists: SAV2001

// The controller catches this and returns:
HTTP 409 Conflict
{"title":"Duplicate Account","detail":"Account already exists: SAV2001"}

What are common mistakes with constraints?

Mistake 1: Not validating at both layers

WRONG: Relying only on database constraints without application-level validation.

JAVA
// WRONG: No validation in the service layer.
// The error message from the database is cryptic.
public Account createAccount(CreateAccountRequest request) {
    Account account = new Account();
    account.setBalance(request.initialDeposit()); // could be negative
    return accountRepository.save(account);
    // Fails with: check constraint "accounts_balance_check" violated
    // The user sees a 500 error with no useful message.
}

CORRECT: Validate in the application and use the database constraint as a safety net.

JAVA
// CORRECT: Validate in the service, constraint is the last line of defense.
public Account createAccount(CreateAccountRequest request) {
    if (request.initialDeposit().compareTo(BigDecimal.ZERO) < 0) {
        throw new IllegalArgumentException(
            "Initial deposit cannot be negative: "
            + request.initialDeposit());
    }
    Account account = new Account();
    account.setBalance(request.initialDeposit());
    return accountRepository.save(account);
}

Mistake 2: ON DELETE CASCADE on financial data

WRONG: Cascade deleting transactions when an account is closed.

SQL
-- WRONG: Deleting an account silently deletes all transaction history
CREATE TABLE transactions_bad (
    txn_id      BIGSERIAL PRIMARY KEY,
    account_id  VARCHAR(10) REFERENCES accounts(account_id)
        ON DELETE CASCADE,
    amount      NUMERIC(15, 2)
);

-- Closing account SAV2001 deletes 500 transaction records.
-- Audit trail is destroyed. Regulators will not be happy.

CORRECT: Use RESTRICT and soft-delete the account.

SQL
-- CORRECT: RESTRICT prevents deletion. Use status column for soft delete.
CREATE TABLE transactions (
    txn_id      BIGSERIAL PRIMARY KEY,
    account_id  VARCHAR(10) NOT NULL
        REFERENCES accounts(account_id)
        ON DELETE RESTRICT,
    amount      NUMERIC(15, 2) NOT NULL
);

-- To "close" an account, update its status. Never delete.
UPDATE accounts SET status = 'CLOSED' WHERE account_id = 'SAV2001';

What should a senior or principal engineer know?

- Deferred constraints. By default, constraints are checked immediately on each statement. With DEFERRABLE INITIALLY DEFERRED, the check happens at commit time. This is useful for circular foreign keys or bulk data loads.

- Exclusion constraints (PostgreSQL). Use EXCLUDE USING gist to prevent overlapping date ranges. For example, prevent two loans from having overlapping active periods on the same account.

- Partial unique indexes. A partial index enforces uniqueness only on a subset of rows. For example, enforce that each customer has at most one ACTIVE savings account: CREATE UNIQUE INDEX idx_one_active_savings ON accounts (customer_id) WHERE account_type = 'SAVINGS' AND status = 'ACTIVE';

- Constraint naming convention. Always name constraints explicitly. Default names like accounts_pkey or accounts_balance_check are generated by the database and vary across engines. Explicit names make error handling and migrations easier.

- Performance impact of constraints. Primary keys and unique constraints create indexes automatically. Foreign keys do not create indexes on the referencing column in PostgreSQL. Always create an index on the foreign key column manually for join performance.

SQL
-- Deferred constraint for circular references
CREATE TABLE departments (
    dept_id     SERIAL PRIMARY KEY,
    manager_id  BIGINT
);

CREATE TABLE employees (
    emp_id      BIGSERIAL PRIMARY KEY,
    dept_id     INT REFERENCES departments(dept_id),
    name        VARCHAR(100)
);

ALTER TABLE departments
    ADD CONSTRAINT fk_dept_manager
    FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
    DEFERRABLE INITIALLY DEFERRED;

-- Now you can insert department and employee in the same transaction
BEGIN;
INSERT INTO departments (dept_id, manager_id) VALUES (1, 100);
INSERT INTO employees (emp_id, dept_id, name)
    VALUES (100, 1, 'Pankaj Architect');
COMMIT; -- constraint checked here, both rows exist
TEXT
-- Without DEFERRABLE, the first INSERT would fail because
-- employee 100 does not exist yet.
-- With DEFERRABLE INITIALLY DEFERRED, both inserts succeed
-- and the constraint is checked at COMMIT.

What are common interview follow-up questions?

  • What is the difference between a primary key and a unique constraint?
  • Can a table have multiple primary keys?
  • What happens when you delete a parent row referenced by a foreign key?
  • What is the difference between ON DELETE CASCADE and ON DELETE RESTRICT?
  • How do you enforce that a column value must be one of a fixed set of values?
  • What is a composite primary key and when do you use it?
  • Does a foreign key automatically create an index in PostgreSQL?
  • What is a deferred constraint and when do you use it?
  • How do you handle constraint violation exceptions in Spring Boot?

Visual Overview

TEXT
  Constraint Types and What They Enforce
  ========================================

  PRIMARY KEY
  +---------------------------+
  | Unique + NOT NULL         |
  | One per table             |
  | Creates index auto        |
  +---------------------------+

  FOREIGN KEY
  +---------------------------+
  | References parent PK      |
  | No orphan rows            |
  | CASCADE / RESTRICT / SET  |
  +---------------------------+

  UNIQUE
  +---------------------------+
  | No duplicates in column   |
  | Allows one NULL           |
  | Multiple per table        |
  | Creates index auto        |
  +---------------------------+

  CHECK
  +---------------------------+
  | Boolean condition         |
  | balance >= 0              |
  | type IN ('SAVINGS',...)   |
  +---------------------------+

  NOT NULL
  +---------------------------+
  | Column cannot be NULL     |
  | Mandatory fields          |
  +---------------------------+
TEXT
  Foreign Key Flow
  =================

  INSERT INTO transactions (account_id, ...)
       |
       v
  Does account_id exist in accounts table?
       |
       +-- YES --> Insert succeeds
       |
       +-- NO  --> ERROR: foreign key violation

  DELETE FROM customers WHERE customer_id = 1
       |
       v
  Any rows in accounts reference customer_id = 1?
       |
       +-- YES + RESTRICT --> ERROR: cannot delete
       +-- YES + CASCADE  --> Delete child rows too
       +-- YES + SET NULL --> Set FK to NULL
       |
       +-- NO  --> Delete succeeds

Key Takeaway

Constraints are the rules that the database enforces automatically, regardless of which application or script inserts the data. A primary key guarantees that every row has a unique, non-null identifier. A foreign key guarantees that every child row points to an existing parent row. Together, they form the backbone of referential integrity in a banking schema.

Unique constraints prevent duplicates on business columns like PAN numbers and email addresses. Check constraints validate business rules at the data layer: balances cannot be negative, account types must be one of a fixed set, and interest rates must be between 0 and 100. NOT NULL constraints mark mandatory fields. These rules fire on every insert and update, not just when your Spring application runs.

In a banking system, constraint violations are expected events, not bugs. When a customer tries to open a duplicate account, the unique constraint fires. Your application catches the DataIntegrityViolationException, translates it to a user-friendly message, and returns an HTTP 409 Conflict. The constraint is the safety net. The application-level validation is the first line of defense.

Senior engineers go further: deferred constraints for circular references, partial unique indexes for conditional uniqueness, exclusion constraints for preventing overlapping date ranges, and explicit constraint naming for clean migrations. In every case, the database is the last guardian of data integrity.

Remember one sentence: validate in the application for good error messages, enforce in the database for guaranteed data integrity.

---

Share
Use ← → arrow keys to navigate