Definition
- Foreign key – a constraint on a column (or columns) that references the primary key of another table, enforcing referential integrity so a child row can only point at an existing parent.
ON DELETE RESTRICT/NO ACTION– blocks deletion of a parent that still has children (the default in most databases).ON DELETE CASCADE– deleting the parent automatically deletes all its children in the same transaction.ON DELETE SET NULL– keeps children but sets the FK column to NULL, severing the link.ON DELETE SET DEFAULT– sets the FK column to its declared default value.ON UPDATE CASCADE– propagates a changed parent key to all children, keeping references valid.
At National Bank of India, every transaction belongs to an account. The account id stored on each transaction is a foreign key pointing back at the accounts table. The bank must never have a transaction pointing at an account that does not exist – that would be an orphan entry in the ledger. ON DELETE decides what happens if someone tries to close Rajesh's account while transactions still reference it: block the closure, automatically remove the transactions too, or leave the transactions but blank out the account link.
Understanding the Concepts
What is a foreign key?
A foreign key is a constraint, not just a column. You declare it with REFERENCES parent(column), and from that moment the database guarantees that every value in the foreign-key column either matches an existing value in the referenced parent column or is NULL.
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(10) NOT NULL,
amount DECIMAL(15,2) NOT NULL,
txn_date TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT fk_txn_account
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);
-- This INSERT succeeds (SAV2001 exists in accounts):
INSERT INTO transactions (account_id, amount) VALUES ('SAV2001', -50000.00);
-- This INSERT fails (SAV9999 does not exist):
INSERT INTO transactions (account_id, amount) VALUES ('SAV9999', -50000.00);
-- ERROR: insert or update violates foreign key constraint
What does ON DELETE RESTRICT do?
The default behaviour. The database blocks any attempt to delete a parent row that still has children.
-- Rajesh's account has transactions referencing it:
DELETE FROM accounts WHERE account_id = 'SAV2001';
-- ERROR: cannot delete because transactions still reference this account.
You must delete or reassign the children first, which forces explicit, audited handling.
What does ON DELETE CASCADE do?
Deleting the parent automatically deletes all its children in the same transaction.
CREATE TABLE notification_prefs (
pref_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(10) NOT NULL,
channel VARCHAR(20) NOT NULL,
CONSTRAINT fk_pref_account
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
ON DELETE CASCADE
);
-- Closing the account also removes all notification preferences:
DELETE FROM accounts WHERE account_id = 'SAV2001';
-- notification_prefs rows for SAV2001 are automatically deleted.
This is appropriate when children have no meaning without the parent, such as preferences or session data. It is dangerous for audit data where history must be preserved.
What does ON DELETE SET NULL do?
Child rows survive but their foreign-key column is set to NULL, severing the link.
CREATE TABLE audit_log (
log_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(10), -- nullable
action VARCHAR(50) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
CONSTRAINT fk_audit_account
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
ON DELETE SET NULL
);
-- Closing the account keeps audit entries but removes the link:
DELETE FROM accounts WHERE account_id = 'SAV2001';
-- audit_log rows for SAV2001 now have account_id = NULL.
What does ON UPDATE CASCADE do?
ON UPDATE CASCADE propagates a change in the parent key down to all children so the references stay valid.
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(10) NOT NULL,
amount DECIMAL(15,2) NOT NULL,
CONSTRAINT fk_txn_account
FOREIGN KEY (account_id) REFERENCES accounts(account_id)
ON UPDATE CASCADE
);
-- Renaming SAV2001 to SAV2001X updates all transactions:
UPDATE accounts SET account_id = 'SAV2001X' WHERE account_id = 'SAV2001';
-- All transactions now reference 'SAV2001X' automatically.
This is one way to cope when a natural primary key changes, though it is exactly the pain a surrogate key avoids.
How do the options compare?
| ON DELETE option | Children | Parent delete | Use case |
|-----------------|----------|--------------|----------|
| RESTRICT (default) | Blocked | Fails | Financial records, audit |
| CASCADE | Deleted | Succeeds | Preferences, sessions |
| SET NULL | FK set to NULL | Succeeds | Audit logs, soft references |
| SET DEFAULT | FK set to default | Succeeds | Rare, specific defaults |
Why should you index the foreign-key column?
Without an index on the FK column, the database may scan the child table to check for referencing rows during parent deletes, and joins on the FK will be slow.
-- Index the foreign key column
CREATE INDEX idx_txn_account_id ON transactions (account_id);
-- Now parent deletes and joins are fast:
EXPLAIN SELECT t.* FROM transactions t
JOIN accounts a ON a.account_id = t.account_id
WHERE a.account_id = 'SAV2001';
-- Uses idx_txn_account_id
How do you implement foreign keys in Java with JPA?
package com.bank.model;
import jakarta.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;
@Entity
@Table(name = "transactions")
public class Transaction {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "txn_id")
private Long txnId;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "account_id", nullable = false,
foreignKey = @ForeignKey(name = "fk_txn_account"))
private Account account;
@Column(name = "amount", nullable = false, precision = 15, scale = 2)
private BigDecimal amount;
@Column(name = "txn_date", nullable = false)
private LocalDateTime txnDate;
}
What about soft deletes in financial systems?
Financial systems usually prefer RESTRICT and soft deletes. Instead of deleting the parent row, set an is_active flag to false. This preserves all history and prevents cascading data loss.
-- Soft delete: mark as inactive instead of deleting
ALTER TABLE accounts ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;
UPDATE accounts SET is_active = FALSE WHERE account_id = 'SAV2001';
-- All transactions still reference the account. History preserved.
What are common mistakes?
The most common mistake is using CASCADE on delete for financial records.
-- Wrong: cascading delete destroys transaction history
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE CASCADE
-- Closing an account silently deletes all its transactions!
-- Correct for financial records: RESTRICT + soft delete
FOREIGN KEY (account_id) REFERENCES accounts(account_id) ON DELETE RESTRICT
-- Cannot close the account while transactions exist. Use soft delete instead.
Another mistake is forgetting to index the FK column, leading to slow cascading deletes and joins.
What are common interview follow-up questions?
- "Can a foreign key reference a non-primary-key column?" Yes, if the referenced column has a unique constraint.
- "Can a foreign key be NULL?" Yes, unless the column is declared NOT NULL. A NULL foreign key means "no parent."
- "What happens with circular foreign keys?" Circular references require deferred constraint checking or NULLable columns, and are generally avoided in practice.
Visual Overview
Parent Table: accounts Child Table: transactions
┌────────────┬──────────┐ ┌────────┬────────────┬──────────┐
│ account_id │ balance │ │ txn_id │ account_id │ amount │
│ (PK) │ │ │ │ (FK) │ │
├────────────┼──────────┤ ├────────┼────────────┼──────────┤
│ SAV2001 │ 500,000 │←──────────│ T001 │ SAV2001 │ -50,000 │
│ CUR3001 │ 200,000 │←──────────│ T002 │ CUR3001 │ +25,000 │
└────────────┴──────────┘ │ T003 │ SAV2001 │ +10,000 │
└────────┴────────────┴──────────┘
DELETE FROM accounts WHERE account_id = 'SAV2001':
RESTRICT: ✗ Blocked (T001, T003 still reference it)
CASCADE: ✓ SAV2001 deleted + T001, T003 deleted
SET NULL: ✓ SAV2001 deleted + T001.account_id = NULL, T003.account_id = NULL
Key Takeaway
A foreign key is a constraint that makes a column in one table reference the primary key of another table, enforcing referential integrity so every child value either matches an existing parent or is NULL. Its job is to prevent orphan rows – a transaction pointing at an account that never existed or was deleted.
The ON DELETE and ON UPDATE clauses define what happens when the parent changes. The default RESTRICT blocks the operation while children exist. CASCADE propagates the delete or update to children. SET NULL keeps children but severs the link by nulling the FK column. Choosing the action is a real design decision: cascading deletes are powerful but dangerous because one delete at the top of a hierarchy can silently wipe out vast amounts of data.
At National Bank of India, transactions reference accounts with ON DELETE RESTRICT so you cannot close an account that still has ledger history, while notification preferences reference accounts with ON DELETE CASCADE because they are meaningless once the account is gone. The FK column is always indexed to avoid full table scans during parent deletes and to speed up joins. For financial records, the team uses RESTRICT plus soft deletes so history is never silently destroyed.
Remember one sentence: a foreign key makes a child point only at an existing parent, and ON DELETE/ON UPDATE decide whether touching the parent is blocked, cascaded, or nulls the link.
---