PRIMARY KEY and SERIAL behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this SERIAL column insertion?

Consider the table users created with a SERIAL primary key. What will be the id values after inserting two rows without specifying id?

PostgreSQL

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

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

SELECT id, name FROM users ORDER BY id;

A [{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]

B [{"id":null,"name":"Alice"},{"id":null,"name":"Bob"}]

C [{"id":0,"name":"Alice"},{"id":1,"name":"Bob"}]

D [{"id":100,"name":"Alice"},{"id":101,"name":"Bob"}]

💡 Hint

SERIAL automatically creates a sequence starting at 1 for the primary key.

The SERIAL type creates an auto-incrementing integer starting at 1 by default. Each inserted row gets the next number automatically.

🧠 Conceptual

intermediate

Why is a PRIMARY KEY required to be unique?

Which of the following best explains why a PRIMARY KEY must be unique in a table?

A To speed up data insertion by avoiding uniqueness checks.

B To uniquely identify each row so that no two rows have the same key value.

C To enable multiple rows to share the same key for grouping.

D To allow NULL values in the key column.

💡 Hint

Think about how databases find a specific row quickly.

A PRIMARY KEY uniquely identifies each row, so no duplicates are allowed. This ensures each row can be referenced precisely.

📝 Syntax

advanced

Which option correctly creates a table with a SERIAL primary key?

Choose the valid SQL statement to create a table products with a SERIAL primary key column product_id and a text column product_name.

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

B CREATE TABLE products (product_id INTEGER SERIAL PRIMARY KEY, product_name TEXT);

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

D CREATE TABLE products (product_id SERIAL PRIMARY, product_name TEXT);

💡 Hint

The SERIAL keyword implies integer type and auto-increment behavior.

Option C correctly uses SERIAL with PRIMARY KEY. Option C has wrong order and type. Option C sets wrong primary key. Option C misses KEY keyword.

❓ optimization

advanced

What is the effect of using SERIAL vs manually creating a sequence for primary key?

Which statement about using SERIAL for primary keys compared to manually creating a sequence and setting default is true?

A Manually creating a sequence is faster than using SERIAL for inserts.

B SERIAL requires manual sequence creation and default setting.

C Using SERIAL disables indexing on the primary key column.

D SERIAL automatically creates a sequence and sets the default, simplifying table creation.

💡 Hint

Think about what SERIAL does behind the scenes.

SERIAL is a shorthand that creates a sequence and sets the default value automatically. It also creates an index for the primary key.

🔧 Debug

expert

Why does this insertion fail with duplicate key error?

Given the table orders with order_id SERIAL PRIMARY KEY, why does this insertion fail? INSERT INTO orders (order_id, product) VALUES (1, 'Book'); Assuming a row with order_id = 1 already exists.

A Because manually inserting a value that duplicates an existing primary key violates uniqueness.

B Because SERIAL columns cannot be inserted manually at all.

C Because the product column is missing a NOT NULL constraint.

D Because the sequence for SERIAL resets after each insert.

💡 Hint

Think about primary key uniqueness and manual value insertion.

Inserting a duplicate primary key value causes a uniqueness violation error. SERIAL allows manual insertion but uniqueness must be maintained.