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.

Practice

1. Which column constraint in PostgreSQL ensures that a column cannot have empty or missing values?

easy

A. PRIMARY KEY

B. NOT NULL

C. CHECK

D. UNIQUE

Solution

1. Step 1: Understand the NOT NULL constraint

   NOT NULL means the column must always have a value; it cannot be left empty.

2. Step 2: Compare with other constraints

   UNIQUE prevents duplicates but allows NULLs; CHECK enforces custom rules; PRIMARY KEY implies NOT NULL and UNIQUE but is more specific.

3. Final Answer:

   NOT NULL -> Option B

4. Quick Check:

   NOT NULL = no empty values [OK]

Hint: NOT NULL means no empty values allowed in the column [OK]

Common Mistakes:

• Confusing UNIQUE with NOT NULL
• Thinking CHECK prevents NULLs
• Assuming PRIMARY KEY is the only way to disallow NULL

2. Which of the following is the correct syntax to add a UNIQUE constraint to the column email in a table users?

easy

A. ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

B. ALTER TABLE users ADD UNIQUE email;

C. ALTER TABLE users ADD UNIQUE (email) CONSTRAINT unique_email;

D. ALTER TABLE users ADD CONSTRAINT email UNIQUE;

Solution

1. Step 1: Recall the syntax for adding UNIQUE constraint

   The correct syntax is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column_name);

2. Step 2: Match the syntax with options

   ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); matches the correct syntax exactly, naming the constraint and specifying the column.

3. Final Answer:

   ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); -> Option A

4. Quick Check:

   ADD CONSTRAINT name UNIQUE (column) [OK]

Hint: Use ADD CONSTRAINT name UNIQUE (column) to add UNIQUE [OK]

Common Mistakes:

• Omitting CONSTRAINT keyword
• Placing UNIQUE before CONSTRAINT name incorrectly
• Not specifying column in parentheses

3. Given the table definition:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  price NUMERIC CHECK (price > 0),
  sku TEXT UNIQUE
);

What happens if you try to insert price = -5 and sku = 'A123'?

medium

A. Insert fails due to UNIQUE constraint violation on sku

B. Insert succeeds because CHECK allows negative values

C. Insert fails due to CHECK constraint violation on price

D. Insert succeeds because price can be negative

Solution

1. Step 1: Analyze the CHECK constraint on price

   The CHECK constraint requires price > 0, so negative values like -5 violate this rule.

2. Step 2: Consider the UNIQUE constraint on sku

   Since 'A123' is new, UNIQUE is not violated here.

3. Final Answer:

   Insert fails due to CHECK constraint violation on price -> Option C

4. Quick Check:

   CHECK (price > 0) rejects negative price [OK]

Hint: CHECK enforces rules; negative price breaks price > 0 [OK]

Common Mistakes:

• Ignoring CHECK constraint effects
• Confusing UNIQUE violation with CHECK violation
• Assuming negative numbers are allowed

4. You have this table:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  age INT CHECK (age >= 18)
);

Which statement will cause an error when inserting data?

medium

A. INSERT INTO employees (email, age) VALUES ('[e-mail]', 25);

B. INSERT INTO employees (email, age) VALUES ('[e-mail]', 20);

C. INSERT INTO employees (email, age) VALUES ('[e-mail]', 18);

D. INSERT INTO employees (email, age) VALUES (NULL, 30);

Solution

1. Step 1: Check NOT NULL constraint on email

   Email cannot be NULL because of NOT NULL constraint.

2. Step 2: Check CHECK constraint on age

   Age must be 18 or older; the ages in A(25), B(20), and D(18) all satisfy this.

3. Final Answer:

   INSERT with NULL email causes error due to NOT NULL constraint -> Option D

4. Quick Check:

   NOT NULL rejects NULL email [OK]

Hint: NOT NULL disallows NULL values, CHECK enforces conditions [OK]

Common Mistakes:

• Focusing on CHECK constraint instead of NOT NULL
• Ignoring NOT NULL on email
• Assuming UNIQUE causes error on first insert

5. You want to create a table orders where the quantity column must be a positive integer and the order_code must be unique and never empty. Which is the best column definition for quantity and order_code?

hard

A. quantity INT CHECK (quantity > 0), order_code TEXT UNIQUE NOT NULL

B. quantity INT NOT NULL, order_code TEXT CHECK (order_code != '')

C. quantity INT CHECK (quantity >= 0), order_code TEXT UNIQUE

D. quantity INT, order_code TEXT NOT NULL UNIQUE CHECK (order_code IS NOT NULL)

Solution

1. Step 1: Ensure quantity is positive integer

   CHECK (quantity > 0) enforces positive values only, better than >= 0 which allows zero.

2. Step 2: Ensure order_code is unique and not empty

   UNIQUE prevents duplicates; NOT NULL disallows empty; this combination ensures order_code is unique and present.

3. Final Answer:

   quantity INT CHECK (quantity > 0), order_code TEXT UNIQUE NOT NULL -> Option A

4. Quick Check:

   CHECK positive + UNIQUE NOT NULL = correct constraints [OK]

Hint: Use CHECK for positive, UNIQUE + NOT NULL for unique non-empty [OK]

Common Mistakes:

• Allowing zero quantity
• Missing NOT NULL on unique column
• Using CHECK without NOT NULL for order_code