UNIQUE constraint in SQL

Introduction

The UNIQUE constraint makes sure that no two rows in a table have the same value in a specific column or group of columns.

When you want to make sure each email address in a user list is different.

When you want to prevent duplicate product codes in an inventory.

When you want to ensure each username in a system is unique.

When you want to avoid repeated phone numbers in a contacts table.

Syntax

SQL

CREATE TABLE table_name (
  column_name data_type UNIQUE
);

-- Or add UNIQUE to existing column:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

The UNIQUE constraint can be added when creating a table or after with ALTER TABLE.

It can apply to one or more columns to make a combined unique key.

Examples

This creates a Users table where each email must be unique.

SQL

CREATE TABLE Users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

This adds a UNIQUE constraint to the product_code column in an existing Products table.

SQL

ALTER TABLE Products
ADD CONSTRAINT unique_product_code UNIQUE (product_code);

This ensures that the combination of first and last name is unique in Employees.

SQL

CREATE TABLE Employees (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  UNIQUE (first_name, last_name)
);

Sample Program

This creates a Customers table with a UNIQUE phone_number. The third insert fails because the phone number is already used.

SQL

CREATE TABLE Customers (
  customer_id INT PRIMARY KEY,
  phone_number VARCHAR(20) UNIQUE
);

INSERT INTO Customers (customer_id, phone_number) VALUES (1, '123-456-7890');
INSERT INTO Customers (customer_id, phone_number) VALUES (2, '987-654-3210');
-- The next insert will fail because phone_number '123-456-7890' already exists
INSERT INTO Customers (customer_id, phone_number) VALUES (3, '123-456-7890');

Important Notes

UNIQUE constraints allow NULL values, but some databases treat multiple NULLs as unique or not unique differently.

If you try to insert a duplicate value in a UNIQUE column, the database will give an error and stop the insert.

UNIQUE constraints help keep your data clean by preventing duplicates.

Summary

UNIQUE constraint prevents duplicate values in a column or group of columns.

It can be set when creating a table or added later with ALTER TABLE.

Trying to insert duplicates in UNIQUE columns causes errors.

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