SQLite UNIQUE Constraint

This guide will teach you how to utilize the SQLite UNIQUE constraint to guarantee that every value within a column or a set of columns is distinct.

Introduction to SQLite UNIQUE constraint

A UNIQUE constraint guarantees that no two values in a column or a set of columns can be identical.

To establish a UNIQUE constraint, you employ the UNIQUE keyword, and you can specify one or more columns to enforce this constraint.

This constraint can be applied either to a specific column or to the entire table. When applied at the table level, you have the option to enforce uniqueness across multiple columns simultaneously.

The following shows how to define a UNIQUE constraint for a column at the column level:

CREATE TABLE table_name(
    ...,
    column_name type UNIQUE,
    ...
);
Code language: SQL (Structured Query Language) (sql)

Or at the table level:

CREATE TABLE table_name(
    ...,
    UNIQUE(column_name)
);
Code language: SQL (Structured Query Language) (sql)

The following illustrates how to define a UNIQUE constraint for multiple columns:

CREATE TABLE table_name(
    ...,
    UNIQUE(column_name1,column_name2,...)
);
Code language: SQL (Structured Query Language) (sql)

Once a UNIQUE constraint is defined, if you attempt to insert or update a value that already exists in the column, SQLite will issue an error and abort the operation.

SQLite UNIQUE constraint examples

Let’s take some examples of using the UNIQUE constraint.

Defining a UNIQUE constraint for one column example

The following statement creates a new table named contacts with a UNIQUE constraint defined for the email column:

CREATE TABLE contacts(
    contact_id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT NOT NULL UNIQUE
);
Code language: SQL (Structured Query Language) (sql)

The following example inserts a new row into the contacts table:

INSERT INTO contacts(first_name,last_name,email)
VALUES ('John','Doe','john.doe@gmail.com');
Code language: SQL (Structured Query Language) (sql)

If you attempt to insert a new contact with the same email, you will get an error message:

INSERT INTO contacts(first_name,last_name,email)
VALUES ('Johnny','Doe','john.doe@gmail.com');
Code language: SQL (Structured Query Language) (sql)

Here is the error message:

Error while executing SQL query on database 'chinook': UNIQUE constraint failed: contacts.email
Code language: SQL (Structured Query Language) (sql)

Defining a UNIQUE constraint for multiple columns example

The following statement creates the shapes table with a UNIQUE constraint defined for the background_color and foreground_color columns:

CREATE TABLE shapes(
    shape_id INTEGER PRIMARY KEY,
    background_color TEXT,
    foreground_color TEXT,
    UNIQUE(background_color,foreground_color)
);
Code language: SQL (Structured Query Language) (sql)

The following statement inserts a new row into the shapes table:

INSERT INTO shapes(background_color,foreground_color)
VALUES('red','green');
Code language: SQL (Structured Query Language) (sql)

The following statement works because of no duplication violation in both background_color and foreground_color columns:

INSERT INTO shapes(background_color,foreground_color)
VALUES('red','blue');
Code language: SQL (Structured Query Language) (sql)

However, the following statement causes an error due to the duplicates in both background_color and foreground_color columns:

INSERT INTO shapes(background_color,foreground_color)
VALUES('red','green');
Code language: SQL (Structured Query Language) (sql)

Here is the error:

Error while executing SQL query on database 'chinook': `UNIQUE` constraint failed: shapes.background_color, shapes.foreground_color
Code language: SQL (Structured Query Language) (sql)

SQLite UNIQUE constraint and NULL

SQLite treats all NULL values are different, therefore, a column with a UNIQUE constraint can have multiple NULL values.

The following statement creates a new table named lists whose email column has a UNIQUE constraint:

CREATE TABLE lists(
    list_id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);
Code language: SQL (Structured Query Language) (sql)

The following statement inserts multiple NULL values into the email column of the lists table:

INSERT INTO lists(email)
VALUES(NULL),(NULL);
Code language: SQL (Structured Query Language) (sql)

Let’s query data from the lists table:

SELECT * FROM lists;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

As you can see, even though the email column has a UNIQUE constraint, it can accept multiple NULL values.

In this guide, you've gained the knowledge of utilizing the SQLite UNIQUE constraint to guarantee that all entries within a specific column or set of columns remain distinct and exclusive.