PRIMARY KEY and SERIAL behavior in PostgreSQL - Mini Project: Build & Apply

Understanding PRIMARY KEY and SERIAL Behavior in PostgreSQL

📖 Scenario: You are creating a simple database table to store information about books in a library. Each book needs a unique identifier that automatically increases for every new book added.

🎯 Goal: Build a PostgreSQL table named books with a column book_id that is a primary key and automatically increments using the SERIAL type. Also add a title column to store the book's name.

📋 What You'll Learn

Create a table named books

Add a column book_id as a SERIAL primary key

Add a column title as VARCHAR(100) not null

💡 Why This Matters

🌍 Real World

Auto-incrementing primary keys are common in databases to uniquely identify records without manual input.

💼 Career

Understanding SERIAL and PRIMARY KEY is essential for database design roles and backend development.

1

Create the books table with book_id column

Write a SQL statement to create a table called books with a column book_id of type SERIAL.

PostgreSQL

-- Your code here

Hint

Use CREATE TABLE books and define book_id SERIAL inside parentheses.

2

Add PRIMARY KEY constraint to book_id

Modify the books table creation SQL to add a PRIMARY KEY constraint on the book_id column.

PostgreSQL

CREATE TABLE books (
    book_id SERIAL,
    -- Add PRIMARY KEY constraint here
);

Hint

Add PRIMARY KEY right after SERIAL for book_id.

3

Add title column with NOT NULL constraint

Add a column named title of type VARCHAR(100) with a NOT NULL constraint to the books table creation SQL.

PostgreSQL

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    -- Add title column here
);

Hint

Define title VARCHAR(100) NOT NULL as a new column in the table.

4

Complete the table creation with both columns

Ensure the final CREATE TABLE books statement includes book_id SERIAL PRIMARY KEY and title VARCHAR(100) NOT NULL columns.

PostgreSQL

-- Your code here

Hint

Check that both columns are defined correctly in the table creation statement.

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