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

Need a 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
);

Need a 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
);

Need a 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

Need a hint?

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