SQLite UNIQUE constraint

A SQLite UNIQUE constraint requires that the values inserted in the columns of a table be unique, it does not allow the insertion of duplicate values. Columns must be defined when creating the table with the uniqueness constraint.

If the table contains only one column identified with a uniqueness constraint, then the values in that column must be unique, without being dependent on the values in other columns. It is similar to a PRIMARY KEY constraint.

If in the created table there are several columns defined with uniqueness constraint, then each row must contain a unique combination of values in the columns identified by the UNIQUE constraint.

UNIQUE constraint can have multiple NULL values. For a UNIQUE constraint, NULL values are considered distinct from all other values, including other NULL values.

Syntax

Here is the syntax of SQLite UNIQUE constraint:

UNIQUE constraint assigned to a column definition.

CREATE TABLE table_name
(
	column_name1 datatype UNIQUE,
	column_name2 datatype,
	...
);

UNIQUE constraint created as a table constraint.

CREATE TABLE table_name
(
	column_name1 datatype,
	column_name2 datatype,
	...,
	UNIQUE(column_name1)	
);

UNIQUE constraint created as a primary key WITHOUT ROWID tables.

CREATE TABLE table_name
(
	column_name1 datatype PRIMARY KEY NOT NULL,
	column_name2 datatype,
	...
) WITHOUT ROWID;

UNIQUE constraint created as unique index in the database.

CREATE TABLE table_name
(
	column_name1 datatype,
	column_name2 datatype,
	...
);

CREATE UNIQUE INDEX unique_index ON table_name(column_name1);

Example

Example of defining a UNIQUE constraint for a one column. The values of column NAME must be unique.

CREATE TABLE courses
(
ID 	INTEGER PRIMARY KEY NOT NULL,
NAME 	VARCHAR(100) NOT NULL UNIQUE,
PRICE 	INTEGER NOT NULL
);

INSERT INTO courses (ID, NAME, PRICE) 
VALUES (1, 'SQLite', 10);
INSERT INTO courses (ID, NAME, PRICE) 
VALUES (2, 'SQL', 20);
INSERT INTO courses (ID, NAME, PRICE) 
VALUES (3, 'MySQL', 30);


SELECT * FROM courses;

Table of courses

ID	NAME	PRICE
1	SQLite	10
2	SQL	20
3	MySQL	30

Now let’s try to insert a row in the courses table, and let the value in the NAME column be SQL.

INSERT INTO courses (ID, NAME, PRICE) 
VALUES (4, 'SQL', 25);

The SQLite database will return the following error:

UNIQUE constraint failed: courses.NAME

Example of defining a UNIQUE constraint for multiple columns. The values of column NAME must be unique.

CREATE TABLE contacts
(
NAME 	TEXT,
EMAIL 	TEXT,
UNIQUE(NAME, EMAIL)
);

INSERT INTO contacts (NAME, EMAIL) 
VALUES ('Paul', '[email protected]');
INSERT INTO contacts (NAME, EMAIL) 
VALUES ('Susan', '[email protected]');

SELECT * FROM contacts;

Table of contacts

NAME	EMAIL
Paul	[email protected]
Susan	[email protected]

Now let’s try to insert a row in the contacts table, and let the value in the EMAIL column be Paul.

INSERT INTO contacts (NAME, EMAIL) 
VALUES ('Paul', '[email protected]');

The SQLite database will return the following error:

UNIQUE constraint failed: contacts.NAME, contacts.EMAIL