UNIQUE constraint in SQL - Mini Project: Build & Apply

Using UNIQUE Constraint in SQL

📖 Scenario: You are creating a simple database table to store user information for a website. Each user must have a unique email address to avoid duplicate accounts.

🎯 Goal: Build a SQL table called users with columns for id, username, and email. Ensure that the email column has a UNIQUE constraint so no two users can have the same email address.

📋 What You'll Learn

Create a table named users

Add an id column as an integer primary key

Add a username column as text

Add an email column as text

Add a UNIQUE constraint on the email column

💡 Why This Matters

🌍 Real World

Websites and applications often require unique emails or usernames to identify users without confusion.

💼 Career

Database developers and administrators use UNIQUE constraints to enforce data integrity and prevent duplicate records.

1

Create the users table with basic columns

Write a SQL statement to create a table called users with three columns: id as an integer primary key, username as text, and email as text.

SQL

-- Create the users table with id, username, and email columns
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT,
    email TEXT
);

Need a hint?

Use CREATE TABLE users and define the columns with their types. Make id the primary key.

2

Add a UNIQUE constraint to the email column

Modify the users table creation SQL to add a UNIQUE constraint on the email column so that no two rows can have the same email.

SQL

-- Add UNIQUE constraint to email column
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT,
    email TEXT UNIQUE
);

Need a hint?

Add the keyword UNIQUE right after the email TEXT column definition.

3

Insert a user with a unique email

Write a SQL INSERT statement to add a user with id 1, username 'alice', and email '[e-mail]' into the users table.

SQL

-- Insert a user with id 1, username 'alice', and email '[e-mail]'
INSERT INTO users (id, username, email) VALUES (1, 'alice', '[e-mail]');

Need a hint?

Use INSERT INTO users (id, username, email) VALUES (1, 'alice', '[e-mail]');

4

Try inserting a user with a duplicate email to see the UNIQUE constraint in action

Write a SQL INSERT statement to add another user with id 2, username 'bob', and the same email '[e-mail]'. This should fail because of the UNIQUE constraint on email.

SQL

-- Insert a user with id 2, username 'bob', and duplicate email '[e-mail]'
INSERT INTO users (id, username, email) VALUES (2, 'bob', '[e-mail]');

Need a hint?

Try inserting a second user with the same email to see the UNIQUE constraint prevent duplicates.