Column constraints (NOT NULL, UNIQUE, CHECK) in PostgreSQL - Step-by-Step Execution

Concept Flow - Column constraints (NOT NULL, UNIQUE, CHECK)

Define Table with Columns

↓

Add NOT NULL Constraint?

Yes→Reject NULL values on insert/update

No↓

Add UNIQUE Constraint?

Yes→Reject duplicate values

No↓

Add CHECK Constraint?

Yes→Validate condition on values

No↓

Table Ready for Data Insertions

↓

On Insert/Update: Enforce Constraints

↓

Accept or Reject Data Based on Constraints

When creating a table, you can add rules (constraints) to columns. These rules check data when you add or change it, making sure it fits your conditions.

Execution Sample

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(20) NOT NULL UNIQUE,
  age INT CHECK (age >= 18)
);

Creates a users table where username cannot be empty or repeated, and age must be 18 or older.

Execution Table

Step	Action	Input Data	Constraint Checked	Result
1	Insert user	{username: 'alice', age: 20}	NOT NULL on username	Pass
2	Insert user	{username: NULL, age: 25}	NOT NULL on username	Fail - NULL not allowed
3	Insert user	{username: 'alice', age: 22}	UNIQUE on username	Fail - duplicate username
4	Insert user	{username: 'bob', age: 17}	CHECK age >= 18	Fail - age too low
5	Insert user	{username: 'carol', age: 30}	All constraints	Pass

💡 Execution stops after each insert attempt with pass or fail based on constraints.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5
users table rows	empty	[{username: 'alice', age: 20}]	[no change]	[no change]	[no change]	[{username: 'alice', age: 20}, {username: 'carol', age: 30}]

Key Moments - 3 Insights

Why does the second insert fail even though age is valid?

Because the username is NULL and the NOT NULL constraint on username rejects NULL values (see execution_table row 2).

Why can't we insert a second user with username 'alice'?

The UNIQUE constraint on username prevents duplicates, so the second 'alice' insert fails (see execution_table row 3).

What happens if age is less than 18?

The CHECK constraint rejects the insert because age must be 18 or older (see execution_table row 4).

Visual Quiz - 3 Questions

Look at the execution table, what is the result of inserting {username: 'bob', age: 17}?

A Pass

B Fail - UNIQUE violation

C Fail - CHECK violation

D Fail - NOT NULL violation

💡 Hint

Check the row where age is 17 and see which constraint fails.

At which step does the users table gain a second row?

A After step 1

B After step 5

C After step 3

D After step 4

💡 Hint

Look at variable_tracker to see when the table rows increase.

If the UNIQUE constraint on username was removed, what would happen at step 3?

A Insert would pass and add duplicate username

B Insert would fail due to NOT NULL

C Insert would fail due to CHECK

D Insert would fail due to PRIMARY KEY

💡 Hint

Refer to execution_table row 3 and think about UNIQUE constraint effect.

Concept Snapshot

Column constraints add rules to table columns:
- NOT NULL: no empty values allowed
- UNIQUE: no duplicate values allowed
- CHECK: custom condition must be true
These constraints run on every insert or update to keep data valid.

Full Transcript

This visual execution shows how column constraints in PostgreSQL work. When you create a table, you can add NOT NULL, UNIQUE, and CHECK constraints to columns. These constraints check data when you insert or update rows. For example, NOT NULL rejects empty values, UNIQUE rejects duplicates, and CHECK enforces a condition like age >= 18. The execution table traces several insert attempts, showing which constraints pass or fail. The variable tracker shows how the table rows change only when inserts pass all constraints. Key moments clarify common confusions about why inserts fail. The quiz tests understanding by asking about specific steps and constraint effects. This helps beginners see how constraints protect data integrity step-by-step.

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