AUTO_INCREMENT vs SERIAL vs IDENTITY in SQL - Visual Side-by-Side Comparison

Concept Flow - AUTO_INCREMENT vs SERIAL vs IDENTITY

Start: Insert new row

↓

Check if PK value given?

Yes→Use given value

No↓

Generate next unique number

↓

Assign number to PK column

↓

Insert row with PK

↓

End

When inserting a new row, the database checks if a primary key (PK) value is given. If not, it generates the next unique number automatically using AUTO_INCREMENT, SERIAL, or IDENTITY depending on the database.

Execution Sample

SQL

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
);

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

This code creates a table with an auto-incrementing id and inserts two rows, letting the database assign ids automatically.

Execution Table

Step	Action	PK value given?	PK assigned	Resulting row
1	Insert ('Alice')	No	1	id=1, name='Alice'
2	Insert ('Bob')	No	2	id=2, name='Bob'
3	Insert with id=5	Yes	5	id=5, name='Custom'
4	Insert ('Charlie')	No	6	id=6, name='Charlie'

💡 Insertion stops when no more rows to insert.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4
Next PK value	1	2	3	6	7

Key Moments - 2 Insights

Why does the next PK value jump from 3 to 6 after inserting a row with id=5?

Because manually inserting id=5 sets the sequence to start after 5, so the next auto-generated PK becomes 6, as shown in execution_table row 4.

What happens if I provide a PK value that already exists?

The database will raise an error due to primary key conflict, preventing duplicate PKs. This is not shown in the table but is important to know.

Visual Quiz - 3 Questions

Look at the execution_table, what PK value is assigned to the second inserted row?

A 1

B 2

C 3

D 5

💡 Hint

Check the 'PK assigned' column in row 2 of the execution_table.

At which step does the PK value get manually assigned instead of auto-generated?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look for 'PK value given?' marked as 'Yes' in the execution_table.

If you insert a new row after step 4, what will be the next PK value assigned?

A 7

B 6

C 5

D 8

💡 Hint

Check the 'Next PK value' in variable_tracker after step 4.

Concept Snapshot

AUTO_INCREMENT, SERIAL, and IDENTITY are ways databases auto-generate unique numbers for primary keys.
AUTO_INCREMENT is common in MySQL.
SERIAL is a PostgreSQL shortcut for auto-incrementing integers.
IDENTITY is the SQL standard way used in SQL Server and others.
They all assign unique numbers automatically when no PK is given.
Manual PK values can override and affect the next auto-generated number.

Full Transcript

When you add a new row to a table with an auto-incrementing primary key, the database checks if you gave a primary key value. If you did, it uses that value. If not, it generates the next unique number automatically. AUTO_INCREMENT is used in MySQL, SERIAL in PostgreSQL, and IDENTITY in SQL Server. For example, inserting 'Alice' and 'Bob' without specifying ids assigns 1 and 2 automatically. If you insert a row with id=5 manually, the next auto-generated id will jump to 6. This ensures no duplicate primary keys. Understanding this helps you manage keys and avoid conflicts when inserting data.