Definition
- PRIMARY KEY – unique, non-null row identifier; implicitly
UNIQUE+NOT NULL; one per table; engine builds an index. - UNIQUE – no two rows share the same value in the constrained columns; multiple unique constraints per table; typically allows nulls.
- NOT NULL – the column must have a value; any null insert or update is rejected.
- DEFAULT – supplies a fallback value when none is given; does not reject, just fills the gap.
- CHECK – a boolean condition the row must satisfy (e.g.,
balance >= 0); rejects rows that make it false. - FOREIGN KEY – a value must match a row in the parent table (referential integrity); defines ON DELETE/ON UPDATE actions.
Think of these as the non-negotiable rules at the National Bank of India data desk. PRIMARY KEY: every account must have one unique account number. UNIQUE: no two customers can register the same email. NOT NULL: an account must have a holder name. DEFAULT: a new account starts ACTIVE unless told otherwise. CHECK: a balance can never be negative. FOREIGN KEY: a transaction must point to a real, existing account, not a made-up one.
Understanding the Concepts
What is a PRIMARY KEY constraint?
PRIMARY KEY enforces entity integrity. It marks the column or set of columns that uniquely identifies each row. It is implicitly both UNIQUE and NOT NULL. The engine builds an index on it for fast lookups.
CREATE TABLE accounts (
account_id VARCHAR(20) PRIMARY KEY,
holder_name VARCHAR(100) NOT NULL,
balance NUMERIC(15,2) NOT NULL DEFAULT 0.00,
status VARCHAR(20) DEFAULT 'ACTIVE'
);
-- Duplicate primary key is rejected
INSERT INTO accounts (account_id, holder_name) VALUES ('SAV2001', 'Rajesh Kumar');
INSERT INTO accounts (account_id, holder_name) VALUES ('SAV2001', 'Priya Sharma');
-- ERROR: duplicate key value violates unique constraint "accounts_pkey"
What is a UNIQUE constraint?
UNIQUE enforces that no two rows share the same value in the constrained column or combination. Unlike PRIMARY KEY, you can have many unique constraints, and unique columns typically allow nulls.
CREATE TABLE customers (
customer_id VARCHAR(20) PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
pan_number VARCHAR(10) UNIQUE
);
-- Duplicate email is rejected
INSERT INTO customers VALUES ('C001', 'Rajesh Kumar', 'rajesh@nbi.com', 'ABCDE1234F');
INSERT INTO customers VALUES ('C002', 'Priya Sharma', 'rajesh@nbi.com', 'FGHIJ5678K');
-- ERROR: duplicate key value violates unique constraint "customers_email_key"
-- Multiple nulls in a unique column are typically allowed
INSERT INTO customers VALUES ('C003', 'Pankaj Singh', NULL, 'KLMNO9012L');
INSERT INTO customers VALUES ('C004', 'Amit Verma', NULL, 'PQRST3456M');
-- Both succeed: nulls are not considered duplicates
What is a NOT NULL constraint?
NOT NULL makes the value mandatory. Any insert or update that leaves the column null is rejected.
-- holder_name is NOT NULL
INSERT INTO accounts (account_id, holder_name, balance)
VALUES ('SAV2003', NULL, 50000.00);
-- ERROR: null value in column "holder_name" violates not-null constraint
-- Updating to NULL is also rejected
UPDATE accounts SET holder_name = NULL WHERE account_id = 'SAV2001';
-- ERROR: null value in column "holder_name" violates not-null constraint
What is a DEFAULT constraint?
DEFAULT supplies a fallback value when an insert does not provide one. It does not reject anything; it fills the gap silently.
CREATE TABLE accounts (
account_id VARCHAR(20) PRIMARY KEY,
holder_name VARCHAR(100) NOT NULL,
balance NUMERIC(15,2) NOT NULL DEFAULT 0.00,
status VARCHAR(20) DEFAULT 'ACTIVE',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert without specifying status or created_at
INSERT INTO accounts (account_id, holder_name)
VALUES ('SAV2004', 'Rajesh Kumar');
SELECT * FROM accounts WHERE account_id = 'SAV2004';
-- balance: 0.00, status: 'ACTIVE', created_at: 2024-06-15 09:00:00
What is a CHECK constraint?
CHECK enforces a domain rule: a boolean condition that each row must satisfy. The database rejects any insert or update that makes the condition false.
CREATE TABLE accounts (
account_id VARCHAR(20) PRIMARY KEY,
holder_name VARCHAR(100) NOT NULL,
balance NUMERIC(15,2) NOT NULL DEFAULT 0.00
CONSTRAINT chk_balance_non_negative CHECK (balance >= 0),
status VARCHAR(20) DEFAULT 'ACTIVE'
CONSTRAINT chk_status_valid CHECK (status IN ('ACTIVE', 'INACTIVE', 'FROZEN'))
);
-- Negative balance is rejected
UPDATE accounts SET balance = -1000.00 WHERE account_id = 'SAV2001';
-- ERROR: new row violates check constraint "chk_balance_non_negative"
-- Invalid status is rejected
UPDATE accounts SET status = 'DELETED' WHERE account_id = 'SAV2001';
-- ERROR: new row violates check constraint "chk_status_valid"
What is a FOREIGN KEY constraint?
FOREIGN KEY enforces referential integrity: a value in the child table must match an existing value in the parent table. It also defines what happens on parent delete or update.
CREATE TABLE transactions (
txn_id BIGSERIAL PRIMARY KEY,
account_id VARCHAR(20) NOT NULL
REFERENCES accounts(account_id)
ON DELETE RESTRICT
ON UPDATE CASCADE,
amount NUMERIC(15,2) NOT NULL,
txn_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert with a valid account: succeeds
INSERT INTO transactions (account_id, amount)
VALUES ('SAV2001', 25000.00);
-- Insert with a non-existent account: rejected
INSERT INTO transactions (account_id, amount)
VALUES ('FAKE999', 10000.00);
-- ERROR: insert or update on table "transactions" violates
-- foreign key constraint: Key (account_id)=(FAKE999) is not present
-- in table "accounts"
-- ON DELETE RESTRICT: cannot delete an account with transactions
DELETE FROM accounts WHERE account_id = 'SAV2001';
-- ERROR: update or delete on table "accounts" violates
-- foreign key constraint on table "transactions"
How do you handle constraint violations in Java?
Rajesh catches constraint violations and converts them into clean application responses.
package com.bank.repository;
import java.math.BigDecimal;
import java.sql.*;
import java.util.logging.Logger;
public class AccountRepository {
private static final Logger LOG = Logger.getLogger(
AccountRepository.class.getName());
private final Connection connection;
public AccountRepository(Connection connection) {
this.connection = connection;
}
public String createAccount(String accountId, String holderName,
BigDecimal initialBalance) {
String sql = "INSERT INTO accounts (account_id, holder_name, balance) "
+ "VALUES (?, ?, ?)";
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setString(1, accountId);
ps.setString(2, holderName);
ps.setBigDecimal(3, initialBalance);
ps.executeUpdate();
return "SUCCESS";
} catch (SQLException e) {
String state = e.getSQLState();
if ("23505".equals(state)) {
return "DUPLICATE_ACCOUNT_ID";
} else if ("23502".equals(state)) {
return "MISSING_REQUIRED_FIELD";
} else if ("23514".equals(state)) {
return "CHECK_CONSTRAINT_VIOLATED";
} else if ("23503".equals(state)) {
return "FOREIGN_KEY_VIOLATION";
}
LOG.severe("Unexpected error: " + e.getMessage());
return "ERROR";
}
}
}
Why should constraints be in the database, not just the application?
The database is the last line of defence shared by every writer. Application validation can be skipped, raced, or duplicated; a database constraint is enforced once, atomically, for every writer.
Why DB Constraints > App-Only Validation: Writer App Validation? DB Constraint? ────────────────── ───────────────── ────────────── Main application Yes Yes Batch import job Maybe Yes Migration script No Yes Direct SQL session No Yes Admin tool Maybe Yes Another service Separate impl Yes (shared)
What should a senior engineer know?
The small price of constraints is a write-time cost and the need to handle violation errors gracefully. A senior should also know that adding a CHECK or NOT NULL to a large existing table validates every row, which can lock the table, so you add it NOT VALID first and VALIDATE later.
-- Adding CHECK to a large table without locking:
ALTER TABLE accounts
ADD CONSTRAINT chk_balance_nn CHECK (balance >= 0) NOT VALID;
-- Instant: does not scan existing rows
ALTER TABLE accounts VALIDATE CONSTRAINT chk_balance_nn;
-- Scans existing rows but does not hold exclusive lock
Visual Overview
┌──────────────────────────────────────────────────────┐ │ SQL Constraint Types │ ├──────────────────────────────────────────────────────┤ │ │ │ PRIMARY KEY ──► Entity integrity (unique + NOT NULL)│ │ ┌─────────────────────────────────────────┐ │ │ │ account_id VARCHAR(20) PRIMARY KEY │ │ │ └─────────────────────────────────────────┘ │ │ │ │ UNIQUE ──► No duplicate values │ │ NOT NULL ──► Value must be present │ │ DEFAULT ──► Fallback when not provided │ │ CHECK ──► Domain rule (balance >= 0) │ │ FOREIGN KEY ──► Must match a parent row │ │ │ │ Enforcement Flow: │ │ │ │ INSERT / UPDATE │ │ │ │ │ ▼ │ │ ┌──────────────────┐ │ │ │ NOT NULL check │── fail ──► REJECT │ │ └────────┬─────────┘ │ │ ▼ │ │ ┌──────────────────┐ │ │ │ CHECK constraint │── fail ──► REJECT │ │ └────────┬─────────┘ │ │ ▼ │ │ ┌──────────────────┐ │ │ │ UNIQUE / PK │── fail ──► REJECT │ │ └────────┬─────────┘ │ │ ▼ │ │ ┌──────────────────┐ │ │ │ FOREIGN KEY │── fail ──► REJECT │ │ └────────┬─────────┘ │ │ ▼ │ │ COMMIT (all passed) │ └──────────────────────────────────────────────────────┘
Key Takeaway
SQL constraints are rules the database itself enforces so that invalid data can never be stored, no matter which application, batch job, or direct SQL session does the writing. There are six core types. PRIMARY KEY enforces entity integrity: a unique, non-null identifier per row backed by an index. UNIQUE forbids duplicate values. NOT NULL makes a value mandatory. DEFAULT quietly supplies a fallback when none is given. CHECK enforces a domain rule like balance >= 0. FOREIGN KEY enforces referential integrity, requiring a value to match an existing parent row.
The reason these belong in the database rather than only in application code is that the database is the last line of defence shared by everyone. Application validation can be skipped by a different service, bypassed by a migration script, or raced under concurrency. A database constraint is enforced once, atomically, for every writer.
At the National Bank of India, the accounts table uses account_id as the PRIMARY KEY, holder_name as NOT NULL, email as UNIQUE, status DEFAULTs to ACTIVE, a CHECK forbids a negative balance, and every transaction has a FOREIGN KEY to a real account. When a batch feed tried to insert a transaction for a non-existent account, the foreign key rejected it at the database rather than silently corrupting the ledger. When two signups raced on the same email, the unique constraint caught the duplicate that the application check had missed.
The small price is a write-time cost and the need to catch constraint violations and turn them into clean responses.
Remember one sentence: PRIMARY KEY identifies, UNIQUE forbids duplicates, NOT NULL demands a value, DEFAULT fills one in, CHECK validates the value, and FOREIGN KEY ties rows to real parents: all enforced by the database itself for every writer.
---