NOT NULL constraint behavior in SQL - Mini Project: Build & Apply

Understanding NOT NULL Constraint Behavior in SQL

📖 Scenario: You are managing a small library database. You want to make sure that every book entry has a title and an author name. These fields should never be empty because they are essential for identifying books.

🎯 Goal: Create a table called Books with columns BookID, Title, and Author. Apply the NOT NULL constraint to Title and Author so that these fields cannot be left empty when inserting data.

📋 What You'll Learn

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

Set BookID as an integer primary key.

Apply the NOT NULL constraint to Title and Author columns.

Insert one valid book record with all fields filled.

Attempt to insert a book record with a NULL value in the Title column to see the constraint in action.

💡 Why This Matters

🌍 Real World

Ensuring important data fields are always filled in databases, like customer names or product IDs.

💼 Career

Database administrators and developers use NOT NULL constraints to maintain data quality and prevent errors.

1

Create the Books table with columns

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

SQL

-- Create the Books table with BookID, Title, and Author columns
-- Your code here

Need a hint?

Use CREATE TABLE Books and define BookID as INTEGER PRIMARY KEY. Add Title and Author as TEXT columns.

2

Add NOT NULL constraints to Title and Author

Modify the Books table creation SQL to add the NOT NULL constraint to the Title and Author columns.

SQL

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

Need a hint?

Add NOT NULL after the TEXT type for both Title and Author columns.

3

Insert a valid book record

Write a SQL INSERT statement to add a book with BookID 1, Title 'The Great Gatsby', and Author 'F. Scott Fitzgerald' into the Books table.

SQL

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL
);

-- Insert a valid book record
-- Your code here

Need a hint?

Use INSERT INTO Books (BookID, Title, Author) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald');

4

Attempt to insert a book with NULL Title

Write a SQL INSERT statement to add a book with BookID 2, Title as NULL, and Author 'Unknown Author' into the Books table. This will demonstrate the NOT NULL constraint behavior.

SQL

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title TEXT NOT NULL,
    Author TEXT NOT NULL
);

INSERT INTO Books (BookID, Title, Author) VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald');

-- Attempt to insert a book with NULL Title
-- Your code here

Need a hint?

Use INSERT INTO Books (BookID, Title, Author) VALUES (2, NULL, 'Unknown Author'); to test the constraint.