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.