PRIMARY KEY and SERIAL behavior in PostgreSQL - Step-by-Step Execution

Concept Flow - PRIMARY KEY and SERIAL behavior

Create Table

↓

Define Column with SERIAL

↓

Auto-create Sequence

↓

Assign Default Nextval from Sequence

↓

Define PRIMARY KEY on Column

↓

Insert Rows

↓

Auto-increment Column Value

↓

Ensure Uniqueness and Not Null

↓

Return Inserted Rows

When creating a table with a SERIAL column and PRIMARY KEY, PostgreSQL auto-creates a sequence for auto-incrementing unique values, enforces uniqueness and not-null constraints, and assigns values automatically on inserts.

Execution Sample

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT
);

INSERT INTO users (name) VALUES ('Alice'), ('Bob');
SELECT * FROM users;

Creates a users table with an auto-incrementing id as primary key, inserts two rows, and selects all rows showing assigned ids.

Execution Table

Step	Action	Sequence Value	id Column Value Inserted	PRIMARY KEY Constraint Check	Output Row
1	Create table with id SERIAL PRIMARY KEY	Sequence created starting at 1	N/A	N/A	Table ready
2	Insert first row ('Alice')	Sequence nextval = 1	1	Pass (unique, not null)	(1, 'Alice')
3	Insert second row ('Bob')	Sequence nextval = 2	2	Pass (unique, not null)	(2, 'Bob')
4	Select all rows	Sequence unchanged	N/A	N/A	[(1, 'Alice'), (2, 'Bob')]
5	Attempt insert with explicit id=2	Sequence unchanged	2	Fail (duplicate primary key)	Error: duplicate key value violates unique constraint
6	Insert third row without id	Sequence nextval = 3	3	Pass (unique, not null)	(3, 'Charlie')

💡 Execution stops after error on duplicate primary key insert; sequence increments only on successful inserts without explicit id.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 5	After Step 6
Sequence Value	1 (created)	2	3	3 (unchanged due to error)	4
id Column Value Inserted	N/A	1	2	2 (attempted duplicate)	3
Table Rows Count	0	1	2	2 (no insert on error)	3

Key Moments - 3 Insights

Why does the sequence value not increment when an insert fails due to duplicate primary key?

Because the sequence increments only when a row is successfully inserted without an explicit id. The failed insert does not consume a sequence number, as shown in execution_table step 5.

What happens if you insert a row with an explicit id that duplicates an existing primary key?

The insert fails with a duplicate key error because the PRIMARY KEY constraint enforces uniqueness, as shown in execution_table step 5.

How does SERIAL relate to sequences and primary keys?

SERIAL creates an integer column with a default value from an auto-created sequence. When combined with PRIMARY KEY, it ensures unique, auto-incremented, not-null values, as shown in the concept_flow and execution_table.

Visual Quiz - 3 Questions

Look at the execution_table at step 3. What is the sequence value after inserting the second row?

A 3

B 2

C 1

D 0

💡 Hint

Check the 'Sequence Value' column in execution_table row for step 3.

At which step does the PRIMARY KEY constraint cause an error?

A Step 2

B Step 4

C Step 5

D Step 6

💡 Hint

Look for 'Fail (duplicate primary key)' in the 'PRIMARY KEY Constraint Check' column.

If you insert a new row without specifying id after step 6, what will be the id value assigned?

A 4

B 3

C 5

D 2

💡 Hint

Check the 'Sequence Value' after step 6 in variable_tracker to see the next sequence number.

Concept Snapshot

PRIMARY KEY and SERIAL in PostgreSQL:
- SERIAL creates an integer column with an auto-incrementing sequence.
- PRIMARY KEY enforces uniqueness and NOT NULL.
- On insert without id, next sequence value is assigned automatically.
- Duplicate primary key inserts cause errors.
- Sequence increments only on successful inserts without explicit id.

Full Transcript

This visual execution trace shows how PostgreSQL handles a table with a SERIAL column defined as PRIMARY KEY. When the table is created, a sequence starts at 1. Each insert without an explicit id uses the next sequence value. The PRIMARY KEY constraint ensures each id is unique and not null. If an insert tries to reuse an existing id, it fails with a duplicate key error. The sequence only increments on successful inserts without explicit ids, preserving uniqueness and order. This behavior is demonstrated step-by-step with inserts and selects, showing sequence values, inserted ids, constraint checks, and resulting rows.

Practice

1. What is the main purpose of a PRIMARY KEY in a PostgreSQL table?

easy

A. To store large text data efficiently

B. To allow duplicate values in a column

C. To automatically generate numbers for a column

D. To uniquely identify each row in the table

Solution

1. Step 1: Understand the role of PRIMARY KEY

   A PRIMARY KEY ensures that each row in a table is unique and can be identified distinctly.

2. Step 2: Compare options with PRIMARY KEY purpose

   Only To uniquely identify each row in the table describes uniqueness, which is the main function of PRIMARY KEY.

3. Final Answer:

   To uniquely identify each row in the table -> Option D

4. Quick Check:

   PRIMARY KEY = unique row ID [OK]

Hint: PRIMARY KEY means unique row identifier [OK]

Common Mistakes:

• Confusing PRIMARY KEY with auto-increment behavior
• Thinking PRIMARY KEY allows duplicates
• Mixing PRIMARY KEY with data storage types

2. Which of the following is the correct way to define a column as an auto-incrementing primary key using SERIAL in PostgreSQL?

easy

A. id INTEGER PRIMARY KEY SERIAL

B. id SERIAL PRIMARY KEY

C. id PRIMARY KEY SERIAL

D. SERIAL id PRIMARY KEY

Solution

1. Step 1: Recall correct SERIAL syntax

   In PostgreSQL, the correct syntax is to declare the column as id SERIAL PRIMARY KEY.

2. Step 2: Check each option's order and syntax

   Only id SERIAL PRIMARY KEY follows the correct order and syntax for SERIAL with PRIMARY KEY.

3. Final Answer:

   id SERIAL PRIMARY KEY -> Option B

4. Quick Check:

   Correct SERIAL syntax = id SERIAL PRIMARY KEY [OK]

Hint: Use 'id SERIAL PRIMARY KEY' order for auto-increment [OK]

Common Mistakes:

• Placing SERIAL after PRIMARY KEY
• Using INTEGER before SERIAL
• Writing SERIAL before column name

3. Given the table definition:

CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);

What will be the result of inserting two rows without specifying id values?

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

medium

A. Two rows with ids 1 and 2 automatically assigned

B. Error because id must be provided

C. Both rows get id 0

D. Rows inserted but id is NULL

Solution

1. Step 1: Understand SERIAL behavior on insert

   SERIAL creates an auto-incrementing integer sequence for the id column, so values are assigned automatically if not provided.

2. Step 2: Predict inserted ids for two rows

   The first insert gets id=1, the second gets id=2 automatically.

3. Final Answer:

   Two rows with ids 1 and 2 automatically assigned -> Option A

4. Quick Check:

   SERIAL auto-increments ids = 1, 2 [OK]

Hint: SERIAL auto-assigns incrementing ids if omitted [OK]

Common Mistakes:

• Expecting to provide id manually
• Thinking id defaults to NULL
• Assuming id starts at 0

4. Consider this table creation:

CREATE TABLE products (
  product_id SERIAL,
  name TEXT,
  PRIMARY KEY (product_id)
);

After successfully inserting a row without specifying product_id (which auto-assigns 1), which of the following statements will cause an error when inserting data?

medium

A. INSERT INTO products (product_id, name) VALUES (1, 'Eraser');

B. INSERT INTO products (product_id, name) VALUES (2, 'Pencil');

C. INSERT INTO products (name) VALUES ('Pen');

D. INSERT INTO products (product_id, name) VALUES (2, 'Notebook');

Solution

1. Step 1: Understand PRIMARY KEY uniqueness

   The product_id is a PRIMARY KEY and must be unique.

2. Step 2: Analyze insert statements for duplicates

   After a prior auto-insert with product_id=1, INSERT INTO products (product_id, name) VALUES (1, 'Eraser'); tries to insert product_id=1 again, causing a duplicate key error.

3. Final Answer:

   INSERT INTO products (product_id, name) VALUES (1, 'Eraser'); -> Option A

4. Quick Check:

   Duplicate PRIMARY KEY value causes error [OK]

Hint: Duplicate PRIMARY KEY values cause insert errors [OK]

Common Mistakes:

• Assuming SERIAL prevents manual duplicate inserts
• Ignoring PRIMARY KEY uniqueness constraint
• Thinking SERIAL auto-updates duplicates

5. You have a table:

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  customer TEXT
);

You manually insert a row with order_id = 100. What happens if you then insert a new row without specifying order_id? Choose the correct behavior.

hard

A. The new row gets order_id = 100, causing a duplicate key error

B. The new row gets order_id = 101, continuing from the highest manual id

C. The new row gets order_id = 1, restarting the sequence

D. The insert fails because manual ids break SERIAL sequence

Solution

1. Step 1: Understand SERIAL sequence behavior with manual inserts

   PostgreSQL sequences do not automatically adjust to manual inserts; explicit values do not advance the sequence.

2. Step 2: Predict next sequence value after manual insert

   Manual insert of 100 does not call nextval, so sequence next value remains 1; auto insert gets order_id=1.

3. Final Answer:

   The new row gets order_id = 1, restarting the sequence -> Option C

4. Quick Check:

   SERIAL sequence unaffected by manual inserts [OK]

Hint: Manual inserts do not advance SERIAL sequence [OK]

Common Mistakes:

• Assuming sequence syncs to MAX(id) and continues from 101
• Expecting duplicate key error on next insert
• Thinking manual insert breaks sequence permanently