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

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

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

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

Hint

Review all constraints together to ensure data integrity.

Practice

1. Why are constraints important in a database?

easy

A. They make the database run faster by skipping checks.

B. They automatically delete old data to save space.

C. They allow users to enter any data without restrictions.

D. They ensure data accuracy and prevent invalid data entry.

Solution

1. Step 1: Understand the role of constraints

   Constraints are rules that keep data correct and reliable by preventing wrong data from being saved.

2. Step 2: Compare options with constraint purpose

   Only They ensure data accuracy and prevent invalid data entry. correctly states that constraints ensure data accuracy and prevent invalid data entry.

3. Final Answer:

   They ensure data accuracy and prevent invalid data entry. -> Option D

4. Quick Check:

   Constraints = Data accuracy and validity [OK]

Hint: Constraints keep data clean and correct [OK]

Common Mistakes:

• Thinking constraints speed up database by skipping checks
• Believing constraints allow any data without limits
• Assuming constraints delete data automatically

2. Which of the following is the correct syntax to add a NOT NULL constraint to a column named email in SQL?

easy

A. ALTER TABLE users ALTER COLUMN email SET NOT NULL;

B. ALTER TABLE users ADD CONSTRAINT email NOT NULL;

C. ALTER TABLE users MODIFY email NOT NULL;

D. ALTER TABLE users CHANGE email TO NOT NULL;

Solution

1. Step 1: Recall SQL syntax for NOT NULL constraint

   To set a column NOT NULL, the standard syntax is ALTER TABLE table ALTER COLUMN column SET NOT NULL.

2. Step 2: Match syntax with options

   ALTER TABLE users ALTER COLUMN email SET NOT NULL; matches the correct syntax. Options A, B, and D use incorrect or invalid syntax.

3. Final Answer:

   ALTER TABLE users ALTER COLUMN email SET NOT NULL; -> Option A

4. Quick Check:

   ALTER COLUMN ... SET NOT NULL = Correct syntax [OK]

Hint: Use ALTER COLUMN ... SET NOT NULL to add NOT NULL [OK]

Common Mistakes:

• Using ADD CONSTRAINT without naming the constraint
• Using MODIFY or CHANGE which are not standard SQL
• Confusing syntax with other SQL dialects

3. Given the table products(id INT PRIMARY KEY, price DECIMAL CHECK (price > 0)), what happens if you run this query?

INSERT INTO products (id, price) VALUES (1, -10);

medium

A. The row is inserted with price -10.

B. The query fails due to the CHECK constraint violation.

C. The price is automatically changed to 0.

D. The query succeeds but price is set to NULL.

Solution

1. Step 1: Understand the CHECK constraint on price

   The CHECK constraint requires price to be greater than 0, so negative values are not allowed.

2. Step 2: Analyze the INSERT statement

   Inserting price = -10 violates the CHECK constraint, so the database rejects the insert and throws an error.

3. Final Answer:

   The query fails due to the CHECK constraint violation. -> Option B

4. Quick Check:

   CHECK constraint rejects invalid data [OK]

Hint: CHECK constraints block invalid values on insert [OK]

Common Mistakes:

• Assuming invalid data is inserted anyway
• Thinking the database auto-corrects invalid values
• Believing NULL is set automatically on violation

4. You have this SQL statement to add a UNIQUE constraint:

ALTER TABLE users ADD UNIQUE (username);

But it fails with an error. What is the most likely cause?

medium

A. The username column contains duplicate values already.

B. The UNIQUE keyword is misspelled.

C. The table users does not exist.

D. UNIQUE constraints cannot be added after table creation.

Solution

1. Step 1: Understand UNIQUE constraint requirements

   Adding a UNIQUE constraint requires all existing values in the column to be unique.

2. Step 2: Identify why ALTER TABLE fails

   If duplicates exist in username, the database rejects the constraint addition with an error.

3. Final Answer:

   The username column contains duplicate values already. -> Option A

4. Quick Check:

   Existing duplicates block UNIQUE constraint [OK]

Hint: Check for duplicates before adding UNIQUE constraint [OK]

Common Mistakes:

• Assuming UNIQUE keyword spelling error
• Ignoring existing duplicate data
• Thinking UNIQUE can't be added after creation

5. You want to ensure that a birthdate column in a persons table only accepts dates in the past. Which constraint would best enforce this rule?

hard

A. UNIQUE (birthdate)

B. NOT NULL

C. CHECK (birthdate < CURRENT_DATE)

D. PRIMARY KEY (birthdate)

Solution

1. Step 1: Identify constraint to limit date values

   To restrict birthdate to past dates, a CHECK constraint comparing birthdate to CURRENT_DATE is needed.

2. Step 2: Evaluate options

   CHECK (birthdate < CURRENT_DATE) uses CHECK (birthdate < CURRENT_DATE), which enforces the rule. NOT NULL only requires a value, UNIQUE and PRIMARY KEY do not limit date range.

3. Final Answer:

   CHECK (birthdate < CURRENT_DATE) -> Option C

4. Quick Check:

   CHECK constraints enforce value rules [OK]

Hint: Use CHECK with date comparison for valid date ranges [OK]

Common Mistakes:

• Using NOT NULL to enforce date range
• Confusing UNIQUE or PRIMARY KEY with value limits
• Not using CURRENT_DATE in CHECK