Definition
- 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.
-- 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)
);
-- 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).
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
);
-- 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".
-- 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.
| Action | Behavior |
|---|---|
RESTRICT (default) | Reject the delete/update if child rows exist. |
CASCADE | Delete/update all child rows automatically. |
SET NULL | Set the foreign key column to NULL in child rows. |
SET DEFAULT | Set the foreign key column to its default value. |
NO ACTION | Same as RESTRICT but checked at the end of the transaction. |
-- 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
);
-- 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).
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
);
-- 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.
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'))
);
-- 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, ...).
-- 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.
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
);
-- 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.
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;
}
}
}
// 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.
// 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.
// 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.
-- 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.
-- 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.
-- 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
-- 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
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 |
+---------------------------+
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.
---