CHECK constraint in SQL - Mini Project: Build & Apply

Using CHECK Constraint to Validate Data in SQL

📖 Scenario: You are creating a simple database table to store information about employees in a company. You want to make sure that the age of each employee is a reasonable number, between 18 and 65 years old.

🎯 Goal: Build a SQL table called Employees with columns for EmployeeID, Name, and Age. Use a CHECK constraint to ensure that the Age is always between 18 and 65.

📋 What You'll Learn

Create a table named Employees with columns EmployeeID (integer), Name (text), and Age (integer).

Add a CHECK constraint on the Age column to allow only values between 18 and 65 inclusive.

Use standard SQL syntax compatible with common databases like PostgreSQL or MySQL.

💡 Why This Matters

🌍 Real World

Companies use CHECK constraints to make sure data like ages, salaries, or IDs stay within valid ranges, preventing mistakes.

💼 Career

Database administrators and developers use CHECK constraints to enforce business rules directly in the database, improving data quality and reliability.

1

Create the Employees table with basic columns

Write a SQL statement to create a table called Employees with three columns: EmployeeID as an integer, Name as text, and Age as an integer.

SQL

CREATE TABLE Employees (
    EmployeeID INT,
    Name TEXT,
    Age INT
);
-- Your code here

Need a hint?

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

2

Add a CHECK constraint for Age limits

Modify the Employees table creation statement to add a CHECK constraint on the Age column that allows only values between 18 and 65 inclusive.

SQL

CREATE TABLE Employees (
    EmployeeID INT,
    Name TEXT,
    Age INT CHECK (Age >= 18 AND Age <= 65)
);
-- Your code here

Need a hint?

Use CHECK (Age >= 18 AND Age <= 65) right after the Age INT column definition.

3

Insert valid employee data

Write a SQL INSERT statement to add an employee with EmployeeID 1, Name 'Alice', and Age 30 into the Employees table.

SQL

INSERT INTO Employees (EmployeeID, Name, Age) VALUES (1, 'Alice', 30);
-- Your code here

Need a hint?

Use INSERT INTO Employees (EmployeeID, Name, Age) VALUES (1, 'Alice', 30); to add the data.

4

Try inserting invalid employee data

Write a SQL INSERT statement to add an employee with EmployeeID 2, Name 'Bob', and Age 17 into the Employees table. This should violate the CHECK constraint.

SQL

INSERT INTO Employees (EmployeeID, Name, Age) VALUES (2, 'Bob', 17);
-- Your code here

Need a hint?

Use INSERT INTO Employees (EmployeeID, Name, Age) VALUES (2, 'Bob', 17); to test the constraint.