SQL 12 🛢️ NOT NULL, UNIQUE, and DEFAULT Constraints

The previous chapter covered the two constraints that define the structure of a relational schema: the primary key and the foreign key. This chapter covers the three constraints that define the rules for individual columns: NOT NULL, UNIQUE, and DEFAULT. They are simpler than the primary and foreign keys, but they are just as important. They are the rules that prevent missing data, duplicate values, and the absence of a sensible default.

The LFCA and SQL chapters in this series have covered the relational model, the data types, and the CREATE TABLE statement. This chapter goes deeper into the three constraints that appear most often in real schemas. Each constraint has its own purpose, its own behavior, and its own trade-offs. The NOT NULL constraint requires a value. The UNIQUE constraint prevents duplicates. The DEFAULT constraint supplies a value when none is given. Together, they shape what the data in a table can look like.

Key point: These constraints are not just validation rules. They are declarations of intent. A NOT NULL constraint says “this column must always have a value.” A UNIQUE constraint says “no two rows can have the same value in this column.” A DEFAULT constraint says “if the application does not provide a value, use this one.” The database enforces these declarations on every insert and update. The application does not need to remember the rules — the database does.

---

Why the three constraints matter

A table with no constraints accepts any data. Any column can be null. Any value can be duplicated. Any insert can omit a column. The table is a container without rules. The three constraints add the rules that make the table useful.

The missing-data problem. A users table without a NOT NULL constraint on email can have users with no email address. The application cannot send password reset emails. The data is incomplete. The NOT NULL constraint prevents the problem by rejecting any insert that does not provide an email.

The duplicate-data problem. A users table without a UNIQUE constraint on email can have two users with the same email. The application cannot determine which user is which. The login system is ambiguous. The UNIQUE constraint prevents the problem by rejecting the duplicate.

The missing-default problem. An orders table without a DEFAULT constraint on status requires the application to provide the status on every insert. If the application forgets, the insert fails or the status is null. The DEFAULT constraint supplies the value when the application does not.

The clarity problem. The constraints document the intent of the schema. A developer reading the schema sees that email is NOT NULL and UNIQUE. The constraints tell the developer what the column means and what values it can hold. The schema is the specification.

The trade-off. Each constraint adds a check on every insert and update. The checks are fast, but they are not free. A table with many constraints has more overhead than a table with none. The overhead is the price of consistency. For an application where the data matters, the price is worth paying.

---

a. The NOT NULL Constraint

The NOT NULL constraint specifies that a column cannot contain a null value. Every row must have a value for the column. The constraint is declared after the column’s data type.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL,
    phone       VARCHAR(20)
);

The first_name, last_name, and email columns are NOT NULL. The phone column is nullable. The difference is intentional: the phone number is optional, but the name and email are required.

The NOT NULL constraint is enforced on every insert and update. An insert that omits a NOT NULL column fails unless the column has a DEFAULT value. An update that sets a NOT NULL column to null fails.

-- This insert fails because first_name is NOT NULL
INSERT INTO employees (employee_id, last_name, email)
VALUES (1, 'Smith', 'smith@example.com');

-- Error: null value in column "first_name" violates not-null constraint

The NOT NULL constraint can be added to an existing column with ALTER TABLE ... ALTER COLUMN ... SET NOT NULL. The database validates the existing data first. If any row has a null value in the column, the statement fails.

ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

The constraint can be removed with ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL.

ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;

The NOT NULL constraint is not the same as the PRIMARY KEY constraint. The primary key implies NOT NULL, but a NOT NULL column is not necessarily a primary key. A table can have many NOT NULL columns.

---

b. The UNIQUE Constraint

The UNIQUE constraint specifies that the values in a column must be unique across all rows. No two rows can have the same value in the column. The constraint is declared after the column’s data type.

CREATE TABLE users (
    user_id  INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email    VARCHAR(100) NOT NULL UNIQUE
);

The username and email columns are UNIQUE. The database rejects any insert or update that would create a duplicate value.

INSERT INTO users (user_id, username, email) VALUES (1, 'alice', 'alice@example.com');
INSERT INTO users (user_id, username, email) VALUES (2, 'alice', 'bob@example.com');

-- Error: duplicate key value violates unique constraint "users_username_key"
-- DETAIL: Key (username)=(alice) already exists.

The UNIQUE constraint allows null values in most databases. Multiple rows can have null in a UNIQUE column because null is not considered equal to null. The behavior varies by database. PostgreSQL and MySQL allow multiple nulls. SQL Server allows only one null. Oracle allows multiple nulls. The difference is worth checking in the target database.

A UNIQUE constraint can be declared at the table level, which is required for composite unique constraints.

CREATE TABLE bookings (
    booking_id INT PRIMARY KEY,
    room_id    INT NOT NULL,
    start_date DATE NOT NULL,
    end_date   DATE NOT NULL,
    UNIQUE (room_id, start_date)
);

The combination of room_id and start_date must be unique. The same room cannot have two bookings that start on the same day.

A UNIQUE constraint creates an index. The index is what makes the uniqueness check fast. The index is also what makes a query on the column fast. The UNIQUE constraint is both a data integrity rule and a performance optimization.

---

c. The DEFAULT Constraint

The DEFAULT constraint specifies a value that is used when the INSERT statement does not provide a value for the column. The constraint is declared after the column’s data type.

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending',
    total       DECIMAL(10, 2) DEFAULT 0
);

The order_date column defaults to the current date. The status column defaults to 'pending'. The total column defaults to 0. When an insert does not provide a value for these columns, the default is used.

INSERT INTO orders (customer_id) VALUES (1);

-- The order_date, status, and total columns are filled with their defaults.
-- order_date = today
-- status = 'pending'
-- total = 0

The DEFAULT constraint does not prevent the application from providing a value. If the insert provides a value, the default is ignored. The default is only used when the column is omitted from the insert.

The DEFAULT constraint can use a constant value, a function, or an expression. CURRENT_DATE, CURRENT_TIMESTAMP, NOW(), and UUID() are common functions. The choice depends on the database.

created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
id         UUID DEFAULT gen_random_uuid()

The DEFAULT constraint can be added to an existing column with ALTER TABLE ... ALTER COLUMN ... SET DEFAULT. The constraint can be removed with ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT.

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

The DEFAULT constraint is not the same as the NOT NULL constraint. A column can be NOT NULL with a DEFAULT, or nullable with a DEFAULT, or NOT NULL without a DEFAULT. The combination determines what happens when the column is omitted.

NOT NULL	DEFAULT	Behavior When Omitted
Yes	Yes	Default is used
Yes	No	Insert fails
No	Yes	Default is used
No	No	Null is used

---

Complete Example Session

This session builds a table that uses all three constraints and demonstrates their behavior.

-- ============================================
-- PART 1: THE TABLE WITH THE THREE CONSTRAINTS
-- ============================================

CREATE TABLE users (
    user_id    SERIAL PRIMARY KEY,
    username   VARCHAR(50) NOT NULL UNIQUE,
    email      VARCHAR(100) NOT NULL UNIQUE,
    full_name  VARCHAR(100) NOT NULL,
    is_active  BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    bio        TEXT
);

-- user_id: auto-incrementing primary key
-- username: required and unique
-- email: required and unique
-- full_name: required
-- is_active: required, defaults to true
-- created_at: required, defaults to current timestamp
-- bio: nullable, no default

-- ============================================
-- PART 2: INSERT WITH DEFAULTS
-- ============================================

INSERT INTO users (username, email, full_name)
VALUES ('alice', 'alice@example.com', 'Alice Johnson');

SELECT user_id, username, is_active, created_at
FROM users WHERE username = 'alice';

-- Output:
--  user_id | username | is_active |          created_at
-- ---------+----------+-----------+-------------------------------
--        1 | alice    | t         | 2026-10-01 12:00:00+00

-- The defaults were applied.

-- ============================================
-- PART 3: TEST NOT NULL
-- ============================================

INSERT INTO users (username, full_name)
VALUES ('bob', 'Bob Smith');

-- Error: null value in column "email" violates not-null constraint

-- The insert fails because email is NOT NULL and has no default.

-- ============================================
-- PART 4: TEST UNIQUE
-- ============================================

INSERT INTO users (username, email, full_name)
VALUES ('alice', 'carol@example.com', 'Carol Williams');

-- Error: duplicate key value violates unique constraint "users_username_key"

-- The insert fails because the username is already taken.

-- ============================================
-- PART 5: TEST UNIQUE ON EMAIL
-- ============================================

INSERT INTO users (username, email, full_name)
VALUES ('carol', 'alice@example.com', 'Carol Williams');

-- Error: duplicate key value violates unique constraint "users_email_key"

-- The insert fails because the email is already taken.

-- ============================================
-- PART 6: TEST DEFAULT OVERRIDE
-- ============================================

INSERT INTO users (username, email, full_name, is_active)
VALUES ('dave', 'dave@example.com', 'Dave Brown', FALSE);

SELECT username, is_active FROM users WHERE username = 'dave';

-- Output:
--  username | is_active
-- ----------+-----------
--  dave     | f

-- The default was overridden by the explicit value.

-- ============================================
-- PART 7: TEST NULL IN UNIQUE COLUMN
-- ============================================

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    sku        VARCHAR(50) UNIQUE,
    name       VARCHAR(100) NOT NULL
);

INSERT INTO products (name) VALUES ('Product A');
INSERT INTO products (name) VALUES ('Product B');

SELECT product_id, sku, name FROM products;

-- Output:
--  product_id | sku  | name
-- ------------+------+-----------
--           1 | null | Product A
--           2 | null | Product B

-- Multiple nulls are allowed in the unique column.

-- ============================================
-- PART 8: TEST COMPOSITE UNIQUE
-- ============================================

CREATE TABLE bookings (
    booking_id INT PRIMARY KEY,
    room_id    INT NOT NULL,
    start_date DATE NOT NULL,
    UNIQUE (room_id, start_date)
);

INSERT INTO bookings VALUES (1, 101, '2026-10-01');
INSERT INTO bookings VALUES (2, 101, '2026-10-02');

INSERT INTO bookings VALUES (3, 101, '2026-10-01');

-- Error: duplicate key value violates unique constraint "bookings_room_id_start_date_key"

-- The combination of room_id and start_date must be unique.

-- ============================================
-- PART 9: ALTER TABLE TO ADD AND DROP CONSTRAINTS
-- ============================================

-- Add a NOT NULL constraint
ALTER TABLE users
ALTER COLUMN bio SET NOT NULL;

-- Remove a NOT NULL constraint
ALTER TABLE users
ALTER COLUMN bio DROP NOT NULL;

-- Add a DEFAULT
ALTER TABLE users
ALTER COLUMN bio SET DEFAULT 'No bio yet';

-- Remove a DEFAULT
ALTER TABLE users
ALTER COLUMN bio DROP DEFAULT;

-- Add a UNIQUE constraint
ALTER TABLE users
ADD CONSTRAINT unique_full_name UNIQUE (full_name);

-- Drop a UNIQUE constraint
ALTER TABLE users
DROP CONSTRAINT unique_full_name;

-- ============================================
-- PART 10: THE CONSTRAINT SUMMARY
-- ============================================

-- NOT NULL: the column must have a value
-- UNIQUE: the values in the column must be unique
-- DEFAULT: the value to use when none is provided
-- PRIMARY KEY: unique + not null + identity
-- FOREIGN KEY: reference to another table

The ten parts cover the table with the three constraints, inserting with defaults, testing NOT NULL, testing UNIQUE, testing unique on email, testing default override, testing null in a unique column, testing a composite unique, altering the table to add and drop constraints, and the constraint summary.

---

Quick Reference

The Three Constraints

Constraint	Purpose
NOT NULL	Column must have a value
UNIQUE	Values must be unique
DEFAULT	Value when none provided

The NOT NULL Behavior

Scenario	Behavior
Insert omits the column	Fails unless a default exists
Insert sets the column to null	Fails
Update sets the column to null	Fails
Column has a default	Default used when omitted

The UNIQUE Behavior

Scenario	Behavior
Insert duplicates a value	Fails
Update duplicates a value	Fails
Insert null	Allowed (multiple nulls in most databases)
Composite unique	The combination must be unique

The DEFAULT Behavior

Scenario	Behavior
Insert omits the column	Default used
Insert provides a value	Default ignored
Insert provides null	Null stored (unless NOT NULL)
Update omits the column	Default not applied

The ALTER TABLE Operations

Operation	Syntax
Add NOT NULL	ALTER TABLE t ALTER COLUMN c SET NOT NULL
Drop NOT NULL	ALTER TABLE t ALTER COLUMN c DROP NOT NULL
Add UNIQUE	ALTER TABLE t ADD CONSTRAINT name UNIQUE (c)
Drop UNIQUE	ALTER TABLE t DROP CONSTRAINT name
Add DEFAULT	ALTER TABLE t ALTER COLUMN c SET DEFAULT v
Drop DEFAULT	ALTER TABLE t ALTER COLUMN c DROP DEFAULT

---

Best Practices

✅ Do This:

-- Use NOT NULL for required columns
email VARCHAR(100) NOT NULL                                     -- ✅

-- Use UNIQUE for natural keys
username VARCHAR(50) NOT NULL UNIQUE                            -- ✅

-- Use DEFAULT for columns with sensible defaults
is_active BOOLEAN NOT NULL DEFAULT TRUE                         -- ✅

-- Name the unique constraint for clarity
CONSTRAINT unique_email UNIQUE (email)                          -- ✅

-- Use a function for timestamps
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP                  -- ✅

❌ Don’t Do This:

-- Don't make everything NOT NULL
-- Optional columns should be nullable.                         -- ❌

-- Don't use UNIQUE on columns that can legitimately duplicate
-- A phone number can be shared.                                -- ❌

-- Don't use a volatile default for a stored column
-- A random value changes every time.                           -- ❌

-- Don't rely on the application to provide defaults
-- Use the DEFAULT constraint.                                  -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Insert fails	Missing NOT NULL column without default	Provide the value or add a default
Duplicate error	UNIQUE violation	Use a unique value
Default not applied	Explicit null passed	Omit the column or use the default
Multiple nulls confusing	Database-specific behavior	Check the database’s behavior
Constraint added fails	Existing data violates the constraint	Fix the data first

---

Real-World Examples

1. NOT NULL

email VARCHAR(100) NOT NULL

2. UNIQUE

username VARCHAR(50) UNIQUE

3. DEFAULT

status VARCHAR(20) DEFAULT 'pending'

4. Composite Unique

UNIQUE (room_id, start_date)

5. Named Unique

CONSTRAINT unique_email UNIQUE (email)

6. Timestamp Default

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

7. UUID Default

id UUID DEFAULT gen_random_uuid()

8. Add NOT NULL

ALTER TABLE users ALTER COLUMN email SET NOT NULL

9. Add DEFAULT

ALTER TABLE users ALTER COLUMN bio SET DEFAULT 'No bio yet'

10. Drop UNIQUE

ALTER TABLE users DROP CONSTRAINT unique_email

---

Visual

The Three Constraints

┌──────────────────────────────────────────────┐
│  NOT NULL                                    │
│    └─ The column must have a value           │
│                                              │
│  UNIQUE                                      │
│    └─ The values must be unique              │
│                                              │
│  DEFAULT                                     │
│    └─ The value when none is provided        │
│                                              │
└──────────────────────────────────────────────┘

The Constraint Combination

┌──────────────────────────────────────────────┐
│  NOT NULL + DEFAULT                          │
│    └─ Required, with a sensible default      │
│    └─ Insert omits → default used            │
│    └─ Insert provides null → fails           │
│                                              │
│  NULLABLE + DEFAULT                          │
│    └─ Optional, with a default               │
│    └─ Insert omits → default used            │
│    └─ Insert provides null → null stored     │
│                                              │
│  NOT NULL, no DEFAULT                        │
│    └─ Required, no default                   │
│    └─ Insert omits → fails                   │
│                                              │
└──────────────────────────────────────────────┘

The Unique Constraint

┌──────────────────────────────────────────────┐
│  UNIQUE                                      │
│                                              │
│  username │ email                            │
│  ─────────┼────────────────                  │
│  alice    │ alice@example.com                │
│  bob      │ bob@example.com                  │
│  carol    │ carol@example.com                │
│                                              │
│  No two rows can have the same username.     │
│  No two rows can have the same email.        │
│  The database rejects the duplicate.         │
│                                              │
└──────────────────────────────────────────────┘

The Default Behavior

┌──────────────────────────────────────────────┐
│  DEFAULT                                     │
│                                              │
│  status VARCHAR(20) DEFAULT 'pending'        │
│                                              │
│  INSERT INTO orders (customer_id) VALUES (1);│
│    └─ status = 'pending' (default)           │
│                                              │
│  INSERT INTO orders (customer_id, status)    │
│    VALUES (1, 'shipped');                    │
│    └─ status = 'shipped' (explicit)          │
│                                              │
│  The default is used only when the column is │
│  omitted from the insert.                    │
│                                              │
└──────────────────────────────────────────────┘

---

Summary

Item	Value
NOT NULL	Column must have a value
UNIQUE	Values must be unique
DEFAULT	Value when none provided
NOT NULL + DEFAULT	Required with a default
UNIQUE + null	Multiple nulls allowed in most databases
Composite unique	UNIQUE (col1, col2)
ALTER TABLE ... SET NOT NULL	Add the constraint
ALTER TABLE ... DROP NOT NULL	Remove the constraint
ALTER TABLE ... SET DEFAULT	Add the default
ALTER TABLE ... DROP DEFAULT	Remove the default

Key takeaways:

• The NOT NULL constraint requires a value in every row. The database rejects any insert or update that sets the column to null. The constraint is declared after the column’s data type .
• The UNIQUE constraint prevents duplicate values. The database rejects any insert or update that would create a duplicate. A UNIQUE constraint creates an index, which makes both the check and the queries on the column fast .
• The DEFAULT constraint supplies a value when none is provided. The default is used only when the column is omitted from the insert. If the insert provides a value, the default is ignored .
• The constraints can be combined. A column can be NOT NULL with a DEFAULT. A column can be UNIQUE and NOT NULL. The combination determines the behavior when the column is omitted.
• A UNIQUE constraint allows null values in most databases. Multiple rows can have null in a UNIQUE column because null is not considered equal to null. The behavior varies by database and should be verified .
• A composite unique constraint is declared at the table level. The combination of the columns must be unique. The UNIQUE (col1, col2) clause is used.
• The constraints can be added and removed with ALTER TABLE. The SET NOT NULL, DROP NOT NULL, SET DEFAULT, DROP DEFAULT, ADD CONSTRAINT, and DROP CONSTRAINT clauses modify the constraints on an existing table.

Remember: The NOT NULL, UNIQUE, and DEFAULT constraints are the rules for individual columns. NOT NULL requires a value. UNIQUE prevents duplicates. DEFAULT supplies a value when none is given. The constraints are declared in the CREATE TABLE statement and enforced by the database on every insert and update. The application does not need to remember the rules. The database does.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!