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
);

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
);

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]');

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]');

Hint

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

Practice

1. What is the main purpose of the UNIQUE constraint in SQL?

easy

A. To automatically generate unique IDs

B. To allow NULL values in a column

C. To speed up query performance

D. To prevent duplicate values in a column or group of columns

Solution

1. Step 1: Understand the UNIQUE constraint and compare to options

   The UNIQUE constraint ensures that all values in a column or a set of columns are different from each other. The other options describe different SQL features, but only "To prevent duplicate values in a column or group of columns" correctly describes the UNIQUE constraint.

2. Final Answer:

   To prevent duplicate values in a column or group of columns -> Option D

3. Quick Check:

   UNIQUE constraint = prevent duplicates [OK]

Hint: UNIQUE means no two rows can have same value [OK]

Common Mistakes:

• Confusing UNIQUE with PRIMARY KEY
• Thinking UNIQUE speeds up queries
• Assuming UNIQUE allows duplicates

2. Which of the following is the correct syntax to add a UNIQUE constraint to an existing column email in a table users?

easy

A. ALTER TABLE users ADD UNIQUE email;

B. ALTER TABLE users ADD CONSTRAINT UNIQUE email;

C. ALTER TABLE users ADD UNIQUE (email);

D. ALTER TABLE users ADD UNIQUE CONSTRAINT (email);

Solution

1. Step 1: Recall ALTER TABLE syntax for UNIQUE and check options

   The correct syntax to add a UNIQUE constraint is: ALTER TABLE table_name ADD UNIQUE (column_name); "ALTER TABLE users ADD UNIQUE (email);" matches exactly. The other options misuse keywords or omit parentheses.

2. Final Answer:

   ALTER TABLE users ADD UNIQUE (email); -> Option C

3. Quick Check:

   ALTER TABLE ADD UNIQUE (column) syntax = ALTER TABLE users ADD UNIQUE (email); [OK]

Hint: Use ADD UNIQUE (column) with parentheses [OK]

Common Mistakes:

• Omitting parentheses around column name
• Using ADD CONSTRAINT UNIQUE without naming constraint
• Misplacing keywords in ALTER TABLE

3. Given the table products with a UNIQUE constraint on product_code, what happens when you run this SQL?

INSERT INTO products (product_code, name) VALUES ('X123', 'Item A');
INSERT INTO products (product_code, name) VALUES ('X123', 'Item B');

medium

A. The second insert fails with a UNIQUE constraint violation error

B. The second insert overwrites the first row

C. Both rows are inserted successfully

D. The database ignores the second insert silently

Solution

1. Step 1: Analyze UNIQUE constraint effect on the insert statements

   The UNIQUE constraint on product_code means no two rows can have the same product_code value. The first insert adds 'X123' successfully. The second insert tries to add the same product_code 'X123', which violates the UNIQUE constraint, causing an error.

2. Final Answer:

   The second insert fails with a UNIQUE constraint violation error -> Option A

3. Quick Check:

   Duplicate insert on UNIQUE column = error [OK]

Hint: Duplicate UNIQUE values cause insert errors [OK]

Common Mistakes:

• Assuming duplicates overwrite existing rows
• Thinking duplicates are ignored silently
• Believing both inserts succeed

4. You tried to add a UNIQUE constraint on column username but got an error. What is the most likely cause?

medium

A. The column username is not indexed

B. The column username contains duplicate values already

C. UNIQUE constraints cannot be added after table creation

D. The table does not exist

Solution

1. Step 1: Understand UNIQUE addition failure and evaluate causes

   Adding a UNIQUE constraint fails if existing data violates uniqueness (duplicates exist). "The column username contains duplicate values already" identifies the cause. A non-existent table is unrelated (assuming it exists); UNIQUE can be added later; indexing is irrelevant or automatic.

2. Final Answer:

   The column username contains duplicate values already -> Option B

3. Quick Check:

   Duplicates prevent adding UNIQUE constraint [OK]

Hint: Check for duplicates before adding UNIQUE [OK]

Common Mistakes:

• Assuming UNIQUE can't be added after creation
• Ignoring existing duplicate data
• Confusing indexing with UNIQUE constraint

5. You want to ensure that the combination of first_name and last_name in the employees table is unique, but duplicates are allowed in each column individually. Which SQL statement correctly enforces this?

hard

A. ALTER TABLE employees ADD UNIQUE (first_name, last_name);

B. ALTER TABLE employees ADD UNIQUE (first_name); ALTER TABLE employees ADD UNIQUE (last_name);

C. ALTER TABLE employees ADD UNIQUE first_name, last_name;

D. ALTER TABLE employees ADD UNIQUE CONSTRAINT employees_unique (first_name);

Solution

1. Step 1: Understand multi-column UNIQUE and analyze options

   A UNIQUE constraint on multiple columns ensures the combination is unique (individual duplicates allowed). "ALTER TABLE employees ADD UNIQUE (first_name, last_name);" correctly adds UNIQUE on the pair. Adding UNIQUE separately disallows individual duplicates; other syntax is wrong or ignores one column.

2. Final Answer:

   ALTER TABLE employees ADD UNIQUE (first_name, last_name); -> Option A

3. Quick Check:

   Multi-column UNIQUE = unique pairs [OK]

Hint: Use UNIQUE on multiple columns for combined uniqueness [OK]

Common Mistakes:

• Adding UNIQUE on each column separately
• Using incorrect syntax without parentheses
• Ignoring the need for combined uniqueness