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.

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