Column constraints (NOT NULL, UNIQUE, CHECK) in PostgreSQL - Deep Dive

Overview - Column constraints (NOT NULL, UNIQUE, CHECK)

What is it?

Column constraints are rules set on table columns to control what kind of data can be stored. NOT NULL ensures a column always has a value. UNIQUE makes sure all values in a column are different. CHECK lets you define custom rules that values must follow.

Why it matters

Without column constraints, databases could store incorrect or inconsistent data, causing errors and confusion. Constraints help keep data clean and trustworthy, which is essential for making good decisions and running applications smoothly.

Where it fits

Before learning column constraints, you should understand basic database tables and columns. After this, you can learn about primary keys, foreign keys, and indexes to manage data relationships and performance.

Mental Model

Core Idea

Column constraints are like rules that guard each column’s data to keep it correct and meaningful.

Think of it like...

Think of a column as a mailbox slot. NOT NULL means the slot can never be empty. UNIQUE means no two mailboxes can have the same mail. CHECK is like a guard who only lets mail with certain stamps through.

┌───────────────┐
│   Table       │
│ ┌───────────┐ │
│ │ Column A  │ │  <-- NOT NULL: always filled
│ │ Column B  │ │  <-- UNIQUE: all different
│ │ Column C  │ │  <-- CHECK: custom rule
│ └───────────┘ │
└───────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding NOT NULL Constraint

Concept: NOT NULL constraint ensures a column cannot have empty values.

When you add NOT NULL to a column, the database will reject any attempt to insert or update a row with no value in that column. This guarantees that every row has meaningful data in that column.

Result

Trying to insert a row without a value in a NOT NULL column causes an error.

Understanding NOT NULL helps prevent missing data, which is a common source of bugs and confusion in databases.

2

Foundation

Understanding UNIQUE Constraint

Concept: UNIQUE constraint makes sure all values in a column are different from each other.

When you declare a column UNIQUE, the database checks every new value against existing ones. If a duplicate is found, it rejects the change. This is useful for columns like email addresses or usernames where duplicates are not allowed.

Result

Inserting a duplicate value in a UNIQUE column causes an error.

Knowing UNIQUE prevents accidental duplicate entries that could break application logic or data integrity.

3

Intermediate

Using CHECK Constraint for Custom Rules

Concept: CHECK constraint lets you define your own rules that column values must follow.

You write a condition inside CHECK, like 'age > 0' or 'status IN (''active'', ''inactive'')'. The database tests this condition for every new or updated row. If the condition fails, the operation is rejected.

Result

Inserting or updating a row that violates the CHECK condition causes an error.

Understanding CHECK empowers you to enforce business rules directly in the database, reducing errors from bad data.

4

Intermediate

Combining Multiple Constraints on Columns

🤔Before reading on: do you think a column can have both NOT NULL and UNIQUE constraints at the same time? Commit to your answer.

Concept: Columns can have more than one constraint to enforce multiple rules simultaneously.

For example, a column can be NOT NULL and UNIQUE, meaning it must always have a value and that value must be different from all others. This is common for identifiers like email or user ID.

Result

The database enforces all constraints together, rejecting any data that breaks any rule.

Knowing constraints can combine helps you design precise data rules that fit real-world needs.

5

Advanced

Constraint Enforcement During Data Changes

🤔Before reading on: do you think constraints are checked only when inserting new rows, or also when updating existing rows? Commit to your answer.

Concept: Constraints are checked both when inserting new rows and when updating existing rows.

When you insert or update data, the database checks all constraints on the affected columns. If any constraint fails, the entire operation is rolled back and an error is returned. This ensures data integrity at all times.

Result

Updates that violate constraints are rejected, keeping the database consistent.

Understanding when constraints are enforced prevents unexpected errors during data updates.

6

Expert

Performance and Limitations of Constraints

🤔Before reading on: do you think adding many constraints always improves database performance? Commit to your answer.

Concept: Constraints improve data quality but can affect performance and have limitations.

While constraints prevent bad data, they add overhead because the database must check rules on every change. UNIQUE constraints often create indexes to speed up checks but can slow down inserts and updates. CHECK constraints can only enforce rules expressible in SQL expressions, not complex logic.

Result

Well-designed constraints balance data integrity and performance; overusing them can slow down the system.

Knowing the tradeoffs helps you design constraints that protect data without hurting performance.

Under the Hood

When data is inserted or updated, PostgreSQL checks each constraint by running validation logic. NOT NULL checks if the value is missing. UNIQUE uses an index to quickly find duplicates. CHECK runs the specified SQL expression on the new value. If any check fails, the operation is aborted before data is saved.

Why designed this way?

Constraints were designed to shift data validation from application code into the database itself, ensuring all clients see consistent rules. Using indexes for UNIQUE constraints speeds up duplicate detection. CHECK constraints use SQL expressions for flexibility and simplicity.

┌───────────────┐
│ Insert/Update │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Constraint    │
│ Checks        │
│ ┌───────────┐ │
│ │ NOT NULL  │ │
│ │ UNIQUE    │ │
│ │ CHECK     │ │
│ └───────────┘ │
└──────┬────────┘
       │ Pass
       ▼
┌───────────────┐
│ Save to Table │
└───────────────┘

Myth Busters - 3 Common Misconceptions

Quick: Does UNIQUE constraint allow multiple NULL values in a column? Commit to yes or no before reading on.

Common Belief: UNIQUE means every value must be different, so no duplicates including NULLs.

Reality: In PostgreSQL, UNIQUE allows multiple NULLs because NULL means unknown, and unknowns are not considered equal.

Why it matters: Assuming UNIQUE forbids multiple NULLs can cause confusion and wrong data assumptions, especially when NULL means missing data.

Quick: Does NOT NULL guarantee that the data is valid or meaningful? Commit to yes or no before reading on.

Common Belief: NOT NULL ensures the data is always valid and useful.

Reality: NOT NULL only ensures a value exists, but it can be meaningless or incorrect data unless combined with other constraints.

Why it matters: Relying on NOT NULL alone can lead to bad data slipping in, causing errors later.

Quick: Can CHECK constraints run complex code or call external functions? Commit to yes or no before reading on.

Common Belief: CHECK constraints can run any code to validate data.

Reality: CHECK constraints only support SQL expressions and cannot execute procedural code or external functions.

Why it matters: Expecting complex logic in CHECK can lead to design mistakes; such logic belongs in application code or triggers.

Expert Zone

1

UNIQUE constraints create implicit indexes, but these indexes can be customized or dropped for special cases.

2

CHECK constraints are evaluated per row, so they cannot enforce rules involving multiple rows or tables.

3

NOT NULL constraints can be deferred in some cases using domain types or triggers, but by default they are immediate.

When NOT to use

Avoid using CHECK constraints for complex validations involving multiple rows or external data; use triggers or application logic instead. For performance-critical columns with many writes, consider carefully before adding UNIQUE constraints due to index overhead.

Production Patterns

In production, NOT NULL is almost always used on essential columns. UNIQUE is common on keys like emails or usernames. CHECK is used for simple validations like ranges or allowed values. Complex validations are often handled by triggers or application code to balance performance and flexibility.

Connections

Primary Key

Primary Key builds on UNIQUE and NOT NULL constraints to uniquely identify rows.

Understanding column constraints clarifies how primary keys enforce uniqueness and presence of data in tables.

Data Validation in Software

Column constraints are a form of data validation at the database level, complementing validation in application code.

Knowing database constraints helps design robust systems where data is checked both in the app and the database for reliability.

Quality Control in Manufacturing

Constraints are like quality control checks ensuring each product (data row) meets standards before shipping (saving).

Seeing constraints as quality gates helps appreciate their role in preventing defects and maintaining trust.

Common Pitfalls

#1 Trying to insert NULL into a NOT NULL column.

Wrong approach:

INSERT INTO users (id, email) VALUES (1, NULL);

Correct approach:

INSERT INTO users (id, email) VALUES (1, '[e-mail]');

Root cause:

Misunderstanding that NOT NULL forbids missing values, so NULL cannot be inserted.

#2 Inserting duplicate values into a UNIQUE column.

Wrong approach:

INSERT INTO products (sku) VALUES ('ABC123');
INSERT INTO products (sku) VALUES ('ABC123');

Correct approach:

INSERT INTO products (sku) VALUES ('ABC123');
INSERT INTO products (sku) VALUES ('XYZ789');

Root cause:

Not realizing UNIQUE constraint prevents duplicates, causing insert errors.

#3 Writing a CHECK constraint with invalid syntax or unsupported logic.

Wrong approach:

ALTER TABLE orders ADD CONSTRAINT check_price CHECK (price > 0 AND is_valid());

Correct approach:

ALTER TABLE orders ADD CONSTRAINT check_price CHECK (price > 0);

Root cause:

Expecting CHECK to run functions or complex code, but it only supports SQL expressions.

Key Takeaways

Column constraints are essential rules that keep database data accurate and reliable.

NOT NULL ensures every row has a value in a column, preventing missing data.

UNIQUE prevents duplicate values, helping maintain distinct records.

CHECK allows custom rules but only supports simple SQL expressions.

Using constraints wisely balances data integrity with database performance.