Data types and constraints in Supabase - Mini Project: Build & Apply

Supabase Table with Data Types and Constraints

📖 Scenario: You are setting up a database table in Supabase to store information about employees in a company. Each employee has an ID, name, email, age, and hire date.

🎯 Goal: Create a table called employees in Supabase with appropriate data types and constraints to ensure data quality.

📋 What You'll Learn

Create a table named employees

Add a column id as an integer primary key

Add a column name as text and not null

Add a column email as text, unique and not null

Add a column age as integer with a check constraint to allow only ages 18 and above

Add a column hire_date as date and not null

💡 Why This Matters

🌍 Real World

Companies use databases like Supabase to store employee information securely and reliably.

💼 Career

Knowing how to define tables with correct data types and constraints is essential for database administrators and backend developers.

1

Create the employees table with id and name columns

Write a SQL statement to create a table called employees with two columns: id as an integer primary key and name as text and not null.

Supabase

-- Your SQL code here to create the employees table with id and name columns

Need a hint?

Use CREATE TABLE employees. Define id as INTEGER PRIMARY KEY and name as TEXT NOT NULL.

2

Add email column with unique and not null constraints

Add a column called email to the employees table with data type TEXT. It must be unique and not null.

Supabase

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    -- Add email column here
);

Need a hint?

Add email TEXT UNIQUE NOT NULL as a column.

3

Add age column with a check constraint for minimum age 18

Add a column called age as INTEGER to the employees table. Add a check constraint to allow only ages 18 or older.

Supabase

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    -- Add age column with check constraint here
);

Need a hint?

Use age INTEGER CHECK (age >= 18) to enforce minimum age.

4

Add hire_date column as date and not null

Add a column called hire_date as DATE type to the employees table. It must be not null.

Supabase

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    age INTEGER CHECK (age >= 18),
    -- Add hire_date column here
);

Need a hint?

Add hire_date DATE NOT NULL as the last column.