Column constraints (NOT NULL, UNIQUE, CHECK) in PostgreSQL - Mini Project: Build & Apply

Column constraints (NOT NULL, UNIQUE, CHECK)

📖 Scenario: You are creating a simple database table to store information about employees in a company. You want to make sure the data is clean and follows some rules.

🎯 Goal: Create a table called employees with columns that have constraints: id cannot be null, email must be unique, and age must be at least 18.

📋 What You'll Learn

Create a table named employees

Add a column id that cannot be NULL

Add a column email that must be UNIQUE

Add a column age with a CHECK constraint to ensure age is 18 or older

💡 Why This Matters

🌍 Real World

Companies use column constraints to keep their data accurate and reliable, like making sure emails are unique or ages are valid.

💼 Career

Database administrators and developers use constraints to enforce rules and prevent bad data from entering the database.

1

Create the employees table with basic columns

Write a SQL statement to create a table called employees with columns id as integer, email as text, and age as integer.

PostgreSQL

CREATE TABLE employees (
    id INTEGER,
    email TEXT,
    age INTEGER
);
-- Your code here

Need a hint?

Use CREATE TABLE employees and list the columns with their data types inside parentheses.

2

Add NOT NULL constraint to id

Modify the employees table creation to add a NOT NULL constraint to the id column so it cannot be empty.

PostgreSQL

CREATE TABLE employees (
    id INTEGER NOT NULL,
    email TEXT,
    age INTEGER
);
-- Your code here

Need a hint?

Add NOT NULL right after the INTEGER type for the id column.

3

Add UNIQUE constraint to email

Modify the employees table creation to add a UNIQUE constraint to the email column so no two employees can have the same email.

PostgreSQL

CREATE TABLE employees (
    id INTEGER NOT NULL,
    email TEXT UNIQUE,
    age INTEGER
);
-- Your code here

Need a hint?

Add UNIQUE right after the TEXT type for the email column.

4

Add CHECK constraint to age to ensure minimum age

Modify the employees table creation to add a CHECK constraint on the age column so that age must be 18 or older.

PostgreSQL

CREATE TABLE employees (
    id INTEGER NOT NULL,
    email TEXT UNIQUE,
    age INTEGER CHECK (age >= 18)
);
-- Your code here

Need a hint?

Add CHECK (age >= 18) right after the INTEGER type for the age column.