SQL Unique Constraint

In this lesson, you will learn how to restrict columns to contain unique values using SQL.

When To Use Unique Constraint

In real-world applications, a user can log in by using his username or email. In this case, we must make these fields unique to be sure that the same username or email cannot be used by more than one user.

In Freeskillacademy, there is already a user having mhamad as his username. That's why if you try to create a new account with the same username will get an error response telling you that the Username is not available as follows.

Unique Constraint In SQL

The unique constraint can be added to the field on table creation or later.

1- Add Unique Constraint On Table Creation

In SQL Server, Oracle, SQLite and Access, you can add the UNIQUE constraint as follows.

CREATE TABLE table_name (
    column_name datatype UNIQUE
);

In MySQL and SQLite, you can add the UNIQUE constraint as follows.

CREATE TABLE table_name (
    column_name datatype,
    UNIQUE (column_name)
);

2- Add Unique Constraint To An Existing Table

You can use the following syntax to add a UNIQUE constraint to an existing table.

ALTER TABLE table_name
ADD UNIQUE (column_name);

You can give the UNIQUE constraint a custom name as follows.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_name);

Preparing A Database

Execute the following query to create a new database.

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;
    

Define Unique Field Example

The following query creates a new table called "users" which has 3 fields:

• id __ the primary key.
• username __ a unique text field.
• country __ a text field.

-- Create the table with the unique constraint
CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    country VARCHAR(100),
    UNIQUE(username)  -- Here we add the UNIQUE constraint to the username field
);
    

-- Create the table
CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    country VARCHAR(100)
);

-- Add the unique constraint to the table
ALTER TABLE users
ADD CONSTRAINT uc_users_username UNIQUE (username);
    

If you view the users table now, it will look like this.

Testing Unique Field Example

The following query inserts 5 records in the users table.
Note: no username is used more than once because this is not allowed.

INSERT INTO users VALUES (null, "Omar", "Morocco");
INSERT INTO users VALUES (null, "Adam", "France");
INSERT INTO users VALUES (null, "Emily", "France");
INSERT INTO users VALUES (null, "Sarah", "Canada");
INSERT INTO users VALUES (null, "Lora", "Australia");
    

If you view the users table now, it will look like this.

The following query will try to insert a new record with a username that already exists.
Of course, it will fail because the username field is UNIQUE.

INSERT INTO users VALUES (null, "Omar", "Egypt");
    

Unique Constraint Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "customers" and check whether it exists or not. the table should contain the following fields:

• id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
• first_name VARCHAR(50)
• last_name VARCHAR(50)
• city VARCHAR(30)
• phone VARCHAR(20)

3- Write a query that makes the email field unique.

4- Write a query that inserts 5 rows in the customers table.

5- Write a query that returns all the records in the customers table.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS customers (
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

ALTER TABLE customers
ADD CONSTRAINT uc_customers_email UNIQUE (email);

INSERT INTO customers (first_name, last_name, email) VALUES
('Jack', 'Reed', 'jackreed@example.com'),
('Emma', 'Clark', 'emmaclark@example.com'),
('Luke', 'Lee', 'lukelee@example.com'),
('Jim', 'Wilson', 'jimwilson@example.com'),
('Lily', 'Brooks', 'lilybrooks@example.com');

SELECT * FROM customers;