PRIMARY KEY and SERIAL behavior in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with an auto-incrementing primary key.

PostgreSQL

CREATE TABLE users (id [1] PRIMARY KEY, name VARCHAR(100));

Drag options to blanks, or click blank then click option'

AINTEGER

BTEXT

CSERIAL

DBOOLEAN

Attempts:

3 left

💡 Hint

Common Mistakes

Using INTEGER without auto-increment will not auto-generate values.

Using TEXT or BOOLEAN types for primary keys is not appropriate.

The SERIAL keyword creates an auto-incrementing integer column, perfect for primary keys.

2fill in blank

medium

Complete the code to insert a new user without specifying the primary key value.

PostgreSQL

INSERT INTO users (name) VALUES ([1]);

Drag options to blanks, or click blank then click option'

A1

BNULL

CDEFAULT

D'Alice'

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to insert a value for the SERIAL primary key manually.

Using DEFAULT or NULL as the name value.

When the primary key is SERIAL, you don't specify it in the insert; just provide the other columns.

3fill in blank

hard

Fix the error in the code to create a table with a primary key that auto-increments.

PostgreSQL

CREATE TABLE orders (order_id [1] PRIMARY KEY, order_date DATE);

Drag options to blanks, or click blank then click option'

ASERIAL

BTEXT

CBOOLEAN

DVARCHAR(20)

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT or VARCHAR for auto-incrementing keys causes errors.

Using BOOLEAN for a primary key is invalid.

Using SERIAL for order_id ensures it auto-increments and can be a primary key.

4fill in blank

hard

Fill both blanks to create a table with a primary key that auto-increments and a unique username.

PostgreSQL

CREATE TABLE accounts (id [1] PRIMARY KEY, username [2] UNIQUE);

Drag options to blanks, or click blank then click option'

ASERIAL

BVARCHAR(50)

CTEXT

DINTEGER

Attempts:

3 left

💡 Hint

Common Mistakes

Using TEXT for username without length limit.

Using INTEGER for username which is not suitable.

The id uses SERIAL for auto-incrementing primary key. The username uses VARCHAR with UNIQUE constraint.

5fill in blank

hard

Fill all three blanks to insert a new account with a username, letting the id auto-increment.

PostgreSQL

INSERT INTO accounts (id, username) VALUES ([1], [2]); -- Use [3] for id to auto-increment

Drag options to blanks, or click blank then click option'

ANULL

B'bob123'

CDEFAULT

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL or 0 for id which may cause errors.

Not quoting the username string.

Use DEFAULT for id to let it auto-increment. Provide the username as a string.

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