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.