Topic 24 of 52
UNIQUE, NOT NULL
Overview
Column constraints enforce data quality rules at the database level. UNIQUE ensures no duplicate values, NOT NULL prevents missing values, and CHECK validates value ranges — catching bad data before it enters the database.
Syntax
sql
-- NOT NULL: column must have a value
email VARCHAR(255) NOT NULL
-- UNIQUE: no duplicate values (NULLs are usually excluded)
email VARCHAR(255) UNIQUE
-- Unique across multiple columns (combination must be unique)
UNIQUE (username, domain)
-- CHECK: custom validation rule
age INT CHECK (age >= 18 AND age <= 120)
status VARCHAR(20) CHECK (status IN ('active','inactive','banned'))
price DECIMAL(10,2) CHECK (price >= 0)
-- Adding constraints to existing tables
ALTER TABLE users ADD CONSTRAINT chk_email_format
CHECK (email LIKE '%@%.%');
ALTER TABLE products ADD CONSTRAINT unq_product_sku UNIQUE (sku);
ALTER TABLE employees ADD CONSTRAINT nn_hire_date NOT NULL;
-- View constraints
SELECT constraint_name, constraint_type FROM information_schema.table_constraints
WHERE table_name = 'users';Common Pitfalls
- NOT NULL enforcement at the DB level is stronger than application-level validation — don't rely only on the app to check for nulls.
- UNIQUE allows multiple NULL values in most databases (since NULL != NULL), so two rows can both have NULL in a UNIQUE column.
- Interview tip: CHECK constraints are evaluated during INSERT and UPDATE — they catch data quality issues at the database level, not just the application.
Real-World Example
A comprehensive constraints setup for a user registration table:
example
sql
CREATE TABLE user_accounts (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(30) NOT NULL
UNIQUE
CHECK (username ~ '^[a-z0-9_]+$'), -- lowercase alphanumeric
email VARCHAR(320) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
role VARCHAR(20) NOT NULL DEFAULT 'user'
CHECK (role IN ('user','moderator','admin')),
age SMALLINT CHECK (age IS NULL OR (age >= 13 AND age <= 150)),
website TEXT CHECK (website IS NULL OR website LIKE 'http%'),
credits INT NOT NULL DEFAULT 0 CHECK (credits >= 0),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Partial unique index (unique emails only for non-deleted users)
CREATE UNIQUE INDEX idx_users_email_active
ON user_accounts(email) WHERE deleted_at IS NULL;