Column definitions and constraints in MySQL - Mini Project: Build & Apply

Create a MySQL Table with Column Definitions and Constraints

📖 Scenario: You are setting up a simple database table to store information about books in a library. Each book has a unique ID, a title, an author, a publication year, and a price.

🎯 Goal: Create a MySQL table named books with columns that have specific data types and constraints to ensure data is stored correctly.

📋 What You'll Learn

Create a table named books

Add a column book_id as an integer and set it as the primary key

Add a column title as a variable character string with a maximum length of 100 characters and not allow NULL values

Add a column author as a variable character string with a maximum length of 50 characters and not allow NULL values

Add a column pub_year as an integer with a constraint that it cannot be less than 1500

Add a column price as a decimal number with two decimal places and a default value of 0.00

💡 Why This Matters

🌍 Real World

Defining tables with proper column types and constraints is essential for storing accurate and reliable data in any database system.

💼 Career

Database administrators and developers must know how to define tables with constraints to maintain data quality and support application requirements.

1

Create the table structure

Write the SQL statement to create a table named books with columns book_id as INT, title as VARCHAR(100), author as VARCHAR(50), pub_year as INT, and price as DECIMAL(5,2).

MySQL

CREATE TABLE books (
    book_id INT,
    title VARCHAR(100),
    author VARCHAR(50),
    pub_year INT,
    price DECIMAL(5,2)
);
-- Your code here

Need a hint?

Start by defining the table and columns with their data types.

2

Add primary key and NOT NULL constraints

Modify the books table creation to set book_id as the primary key and add NOT NULL constraints to title and author columns.

MySQL

CREATE TABLE books (
    book_id INT,
    title VARCHAR(100),
    author VARCHAR(50),
    pub_year INT,
    price DECIMAL(5,2),
    -- Your code here to add primary key and NOT NULL constraints
);

Need a hint?

Use PRIMARY KEY after book_id INT and add NOT NULL after title VARCHAR(100) and author VARCHAR(50).

3

Add a CHECK constraint for publication year

Add a CHECK constraint to the pub_year column so that the year cannot be less than 1500.

MySQL

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50) NOT NULL,
    pub_year INT CHECK (pub_year >= 1500),
    price DECIMAL(5,2)
);
-- Your code here to add CHECK constraint

Need a hint?

Use CHECK (pub_year >= 1500) after the pub_year INT column definition.

4

Set default value for price column

Add a DEFAULT value of 0.00 to the price column in the books table.

MySQL

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(50) NOT NULL,
    pub_year INT CHECK (pub_year >= 1500),
    price DECIMAL(5,2) DEFAULT 0.00
);
-- Your code here to add DEFAULT value

Need a hint?

Add DEFAULT 0.00 after the price DECIMAL(5,2) column definition.