Why Column constraints (NOT NULL, UNIQUE, CHECK) in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if your database could catch your mistakes before they happen?

The Scenario

Imagine you have a big spreadsheet where you must enter customer data by hand. You want to make sure every customer has a phone number, no two customers share the same email, and ages are always positive numbers.

The Problem

Doing this by eye is slow and mistakes happen often. You might miss empty phone numbers, accidentally type the same email twice, or enter impossible ages. Fixing these errors later wastes time and causes confusion.

The Solution

Column constraints like NOT NULL, UNIQUE, and CHECK automatically enforce these rules for you. The database stops wrong data from entering, so your data stays clean and reliable without extra effort.

Before vs After

✗ Before

INSERT INTO customers (name, email, phone, age) VALUES ('Alice', '[e-mail]', NULL, -5);

✓ After

CREATE TABLE customers (name TEXT, email TEXT UNIQUE, phone TEXT NOT NULL, age INT CHECK (age > 0));

What It Enables

It lets you trust your data and focus on using it, not fixing it.

Real Life Example

A company uses UNIQUE to ensure no two users register with the same username, NOT NULL to require emails for contact, and CHECK to keep ages within a valid range.

Key Takeaways

Constraints prevent bad data from entering your database.

NOT NULL ensures important fields are never empty.

UNIQUE and CHECK keep data accurate and consistent.