Primary key declaration in MySQL - Mini Project: Build & Apply

Defining a Primary Key in MySQL

📖 Scenario: You are creating a simple database table to store information about books in a library. Each book must have a unique identifier so that it can be easily found and managed.

🎯 Goal: Create a MySQL table called books with columns for book_id, title, and author. Define book_id as the primary key to ensure each book has a unique ID.

📋 What You'll Learn

Create a table named books

Add columns book_id (integer), title (varchar 100), and author (varchar 100)

Declare book_id as the primary key

💡 Why This Matters

🌍 Real World

Primary keys are essential in databases to uniquely identify each record, like a library book's unique ID.

💼 Career

Database developers and administrators use primary keys to organize and maintain data integrity in real-world applications.

1

Create the books table with columns

Write a MySQL CREATE TABLE statement to create a table called books with columns book_id as INT, title as VARCHAR(100), and author as VARCHAR(100).

MySQL

CREATE TABLE books (
    book_id INT,
    title VARCHAR(100),
    author VARCHAR(100)
    -- Your code here
);

Need a hint?

Use CREATE TABLE books (column definitions) to start. Define each column with its name and data type.

2

Add primary key declaration

Add a primary key constraint to the book_id column inside the CREATE TABLE books statement by adding PRIMARY KEY (book_id).

MySQL

CREATE TABLE books (
    book_id INT,
    title VARCHAR(100),
    author VARCHAR(100),
    PRIMARY KEY (book_id)  -- Your code here
);

Need a hint?

Place PRIMARY KEY (book_id) after the column definitions, separated by commas.

3

Add NOT NULL to primary key column

Modify the book_id column to include NOT NULL so it cannot have empty values, by changing book_id INT to book_id INT NOT NULL.

MySQL

CREATE TABLE books (
    book_id INT NOT NULL,  -- Your code here
    title VARCHAR(100),
    author VARCHAR(100),
    PRIMARY KEY (book_id)
);

Need a hint?

Add NOT NULL right after the data type for book_id.

4

Add AUTO_INCREMENT to primary key

Add AUTO_INCREMENT to the book_id column so MySQL automatically assigns a unique number to each new book. Change book_id INT NOT NULL to book_id INT NOT NULL AUTO_INCREMENT.

MySQL

CREATE TABLE books (
    book_id INT NOT NULL AUTO_INCREMENT,  -- Your code here
    title VARCHAR(100),
    author VARCHAR(100),
    PRIMARY KEY (book_id)
);

Need a hint?

Add AUTO_INCREMENT after NOT NULL to make the ID auto-generate.