AUTO_INCREMENT vs SERIAL vs IDENTITY in SQL - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Difference between AUTO_INCREMENT and SERIAL

Which statement correctly describes the difference between AUTO_INCREMENT in MySQL and SERIAL in PostgreSQL?

A AUTO_INCREMENT is a column attribute in MySQL, while SERIAL is a pseudo-type in PostgreSQL that creates a sequence and sets a default value.

B AUTO_INCREMENT and SERIAL are identical and interchangeable in all SQL databases.

C AUTO_INCREMENT requires manual sequence creation, but SERIAL automatically increments without sequences.

D SERIAL is used only for string columns, while AUTO_INCREMENT is for numeric columns.

💡 Hint

Think about how each database handles automatic numbering internally.

In MySQL, AUTO_INCREMENT is a column attribute that automatically increments numeric values. In PostgreSQL, SERIAL is a pseudo-type that creates an integer column, a sequence, and sets the default value to the next sequence number.

❓ query_result

intermediate

Output of IDENTITY column insertion

Given the following SQL Server table and insert statements, what will be the output of the SELECT * FROM Employees; query?

CREATE TABLE Employees (
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Name VARCHAR(50)
);

INSERT INTO Employees (Name) VALUES ('Alice');
INSERT INTO Employees (Name) VALUES ('Bob');

A

ID | Name
1  | Alice
2  | Bob

B

ID | Name
0  | Alice
1  | Bob

C

ID | Name
1  | Alice
1  | Bob

D

ID | Name
NULL | Alice
NULL | Bob

💡 Hint

Remember how IDENTITY columns auto-increment starting from the seed value.

The IDENTITY(1,1) means the first value is 1 and increments by 1 for each new row. So Alice gets ID 1, Bob gets ID 2.

📝 Syntax

advanced

Correct syntax for SERIAL column in PostgreSQL

Which of the following SQL statements correctly creates a table with a SERIAL primary key column in PostgreSQL?

A CREATE TABLE users (id INT SERIAL PRIMARY KEY, username VARCHAR(50));

B CREATE TABLE users (id SERIAL PRIMARY KEY, username VARCHAR(50));

C CREATE TABLE users (id SERIAL, username VARCHAR(50) PRIMARY KEY);

D CREATE TABLE users (id SERIAL PRIMARY, username VARCHAR(50));

💡 Hint

Check the correct placement of PRIMARY KEY and the data type.

Option B uses id SERIAL PRIMARY KEY which is the correct syntax. Option B wrongly adds INT before SERIAL. Option B sets username as primary key, not id. Option B uses invalid keyword PRIMARY.

❓ optimization

advanced

Choosing the best auto-increment method for high concurrency

You are designing a high-traffic web application using PostgreSQL. Which auto-increment method is best to avoid contention and ensure fast inserts?

A Use <code>SERIAL</code> columns with default sequences.

B Use <code>AUTO_INCREMENT</code> attribute as in MySQL.

C Use <code>IDENTITY</code> columns introduced in PostgreSQL 10+.

D Manually create a sequence and use <code>nextval()</code> in insert statements.

💡 Hint

Consider modern PostgreSQL features designed for concurrency.

PostgreSQL 10+ supports IDENTITY columns which are standard-compliant and better optimized for concurrency than SERIAL. AUTO_INCREMENT is not supported in PostgreSQL.

🔧 Debug

expert

Why does this MySQL AUTO_INCREMENT insert fail?

Consider this MySQL table and insert statements:

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT PRIMARY KEY,
  product VARCHAR(50)
);

INSERT INTO orders (order_id, product) VALUES (NULL, 'Book');
INSERT INTO orders (order_id, product) VALUES (5, 'Pen');
INSERT INTO orders (order_id, product) VALUES (NULL, 'Notebook');

After these inserts, what will be the order_id values in the table?

A NULL, 5, NULL

B 1, 6, 7

C 1, 1, 2

D 1, 5, 6

💡 Hint

Remember how MySQL handles explicit values and NULL with AUTO_INCREMENT.

First insert with NULL gets order_id 1. Second insert explicitly sets order_id 5. Third insert with NULL gets next auto-increment value after 5, which is 6.

Practice

1. Which keyword is used in MySQL to automatically generate unique numbers for a primary key column?

easy

A. IDENTITY

B. SERIAL

C. SEQUENCE

D. AUTO_INCREMENT

Solution

1. Step 1: Understand MySQL auto-numbering

   MySQL uses AUTO_INCREMENT to generate unique numbers automatically for columns like primary keys.

2. Step 2: Compare with other keywords

   SERIAL is PostgreSQL-specific, IDENTITY is SQL standard used in SQL Server and newer PostgreSQL versions, and SEQUENCE is a separate object, not a column keyword.

3. Final Answer:

   AUTO_INCREMENT -> Option D

4. Quick Check:

   MySQL auto-number = AUTO_INCREMENT [OK]

Hint: MySQL uses AUTO_INCREMENT for auto numbering [OK]

Common Mistakes:

• Confusing SERIAL with MySQL syntax
• Using IDENTITY in MySQL
• Thinking SEQUENCE is a column keyword

2. Which of the following is the correct syntax to create an auto-incrementing primary key column in PostgreSQL using the SERIAL keyword?

easy

A. CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50));

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

C. CREATE TABLE users (id IDENTITY PRIMARY KEY, name VARCHAR(50));

D. CREATE TABLE users (id SEQUENCE PRIMARY KEY, name VARCHAR(50));

Solution

1. Step 1: Identify PostgreSQL SERIAL syntax

   In PostgreSQL, SERIAL is used as a shorthand to create an integer column that auto-increments.

2. Step 2: Check syntax correctness

   CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50)); uses id SERIAL PRIMARY KEY, which is valid. Options B and C use MySQL and SQL Server syntax incorrectly in PostgreSQL. CREATE TABLE users (id SEQUENCE PRIMARY KEY, name VARCHAR(50)); uses SEQUENCE incorrectly as a column type.

3. Final Answer:

   CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50)); -> Option A

4. Quick Check:

   SERIAL syntax in PostgreSQL = CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(50)); [OK]

Hint: PostgreSQL uses SERIAL for auto-increment columns [OK]

Common Mistakes:

• Using AUTO_INCREMENT in PostgreSQL
• Using IDENTITY without parentheses
• Treating SEQUENCE as a column type

3. Given the following SQL Server table creation statement, what will be the output of inserting two rows without specifying the ID column?

CREATE TABLE products (id INT IDENTITY(1,1) PRIMARY KEY, name VARCHAR(20));
INSERT INTO products (name) VALUES ('Pen');
INSERT INTO products (name) VALUES ('Pencil');
SELECT * FROM products;

medium

A. [{"id": null, "name": "Pen"}, {"id": null, "name": "Pencil"}]

B. [{"id": 0, "name": "Pen"}, {"id": 1, "name": "Pencil"}]

C. [{"id": 1, "name": "Pen"}, {"id": 2, "name": "Pencil"}]

D. Syntax error on INSERT statements

Solution

1. Step 1: Understand IDENTITY behavior in SQL Server

   The IDENTITY(1,1) means the ID column starts at 1 and increments by 1 automatically for each new row.

2. Step 2: Predict inserted values

   Inserting two rows without specifying ID will assign IDs 1 and 2 respectively. The SELECT will show these IDs with names.

3. Final Answer:

   [{"id": 1, "name": "Pen"}, {"id": 2, "name": "Pencil"}] -> Option C

4. Quick Check:

   IDENTITY auto increments IDs starting at 1 [OK]

Hint: IDENTITY(1,1) starts numbering from 1 incrementing by 1 [OK]

Common Mistakes:

• Assuming ID starts at 0
• Expecting null IDs without insert values
• Thinking INSERT needs ID value explicitly

4. You try to create a table in PostgreSQL with this statement:

CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY, order_date DATE);

What is the error and how to fix it?

medium

A. AUTO_INCREMENT is invalid in PostgreSQL; use SERIAL or IDENTITY instead

B. Missing parentheses after AUTO_INCREMENT

C. order_id must be VARCHAR for AUTO_INCREMENT

D. No error; statement is correct

Solution

1. Step 1: Identify AUTO_INCREMENT support

   PostgreSQL does not support AUTO_INCREMENT; this is a MySQL keyword.

2. Step 2: Correct PostgreSQL syntax

   Use SERIAL or GENERATED AS IDENTITY for auto-incrementing columns in PostgreSQL.

3. Final Answer:

   AUTO_INCREMENT is invalid in PostgreSQL; use SERIAL or IDENTITY instead -> Option A

4. Quick Check:

   PostgreSQL uses SERIAL or IDENTITY, not AUTO_INCREMENT [OK]

Hint: PostgreSQL rejects AUTO_INCREMENT; use SERIAL or IDENTITY [OK]

Common Mistakes:

• Trying to use MySQL syntax in PostgreSQL
• Assuming AUTO_INCREMENT works everywhere
• Ignoring error messages about syntax

5. You want to migrate a MySQL table with AUTO_INCREMENT primary key to PostgreSQL. Which approach correctly preserves auto-increment behavior?

hard

A. Keep AUTO_INCREMENT keyword as is in PostgreSQL

B. Replace AUTO_INCREMENT with SERIAL type for the primary key column

C. Use IDENTITY without specifying start value

D. Manually insert IDs for each row without auto-increment

Solution

1. Step 1: Understand MySQL to PostgreSQL migration

   MySQL uses AUTO_INCREMENT, but PostgreSQL does not support it directly.

2. Step 2: Use PostgreSQL equivalent

   PostgreSQL uses SERIAL as a shorthand for auto-increment integer columns, preserving behavior.

3. Final Answer:

   Replace AUTO_INCREMENT with SERIAL type for the primary key column -> Option B

4. Quick Check:

   MySQL AUTO_INCREMENT -> PostgreSQL SERIAL [OK]

Hint: Use SERIAL in PostgreSQL to replace MySQL AUTO_INCREMENT [OK]

Common Mistakes:

• Leaving AUTO_INCREMENT keyword unchanged
• Using IDENTITY without proper syntax
• Manually managing IDs instead of auto-increment