Why constraints matter in SQL - See It in Action

Why Constraints Matter in SQL

📖 Scenario: You are creating a simple database for a small library. You want to make sure the data is accurate and organized. Constraints help keep the data clean and prevent mistakes.

🎯 Goal: Build a table with constraints to ensure data integrity for a library's book records.

📋 What You'll Learn

Create a table named Books with columns BookID, Title, Author, and YearPublished.

Add a primary key constraint on BookID to uniquely identify each book.

Add a NOT NULL constraint on Title and Author to ensure these fields are always filled.

Add a CHECK constraint on YearPublished to allow only years from 1450 to the current year (2024).

💡 Why This Matters

🌍 Real World

Libraries, bookstores, and many businesses use constraints to keep their data clean and reliable.

💼 Career

Understanding constraints is essential for database design and data quality control in many IT and data roles.

1

Create the Books table with basic columns

Write a SQL statement to create a table called Books with columns BookID as an integer, Title as text, Author as text, and YearPublished as an integer.

SQL

CREATE TABLE Books (
    BookID INT,
    Title TEXT,
    Author TEXT,
    YearPublished INT
);
-- Your code here

Need a hint?

Use CREATE TABLE followed by the table name and define each column with its data type.

2

Add primary key and NOT NULL constraints

Modify the Books table creation to add a PRIMARY KEY constraint on BookID and NOT NULL constraints on Title and Author.

SQL

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    YearPublished INT
);
-- Your code here

Need a hint?

Add PRIMARY KEY after BookID INT and NOT NULL after Title TEXT and Author TEXT.

3

Add a CHECK constraint for YearPublished

Add a CHECK constraint on YearPublished to allow only values between 1450 and 2024 inclusive in the Books table creation.

SQL

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    YearPublished INT CHECK (YearPublished >= 1450 AND YearPublished <= 2024)
);
-- Your code here

Need a hint?

Use CHECK (YearPublished >= 1450 AND YearPublished <= 2024) after the YearPublished INT column.

4

Complete the Books table with all constraints

Ensure the Books table creation includes BookID as INT PRIMARY KEY, Title and Author as TEXT NOT NULL, and YearPublished as INT with a CHECK constraint for years between 1450 and 2024.

SQL

CREATE TABLE Books (
    BookID INT PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL,
    YearPublished INT CHECK (YearPublished >= 1450 AND YearPublished <= 2024)
);
-- Your code here

Need a hint?

Review all constraints together to ensure data integrity.