SQLite | Indexes | Creating a UNIQUE Index

Tags:

A regular index can include duplicate values. A unique index prevents duplicates in its indexed columns. This article explains how to use unique indexes.

Creating a UNIQUE Index

Use the following syntax.

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

SQLite cannot create a unique index if the target columns already contain duplicate values. After the index is created, rows that duplicate an existing indexed value cannot be inserted.

For an index covering multiple columns, individual columns may contain duplicates as long as the complete combination of indexed values is unique.

Create a table for the example.

create table user (name text, old integer, address text);

sqlite> create table user (name text, old integer, address text);
sqlite>

Insert the following data.

insert into user values ('devkuma', 28, 'Seoul');
insert into user values ('kimkc', 22, 'Busan');
insert into user values ('araikuma', 32, 'Seoul');
insert into user values ('happykuma', 23, 'Seoul');

sqlite> insert into user values ('devkuma', 28, 'Seoul');
sqlite> insert into user values ('kimkc', 22, 'Busan');
sqlite> insert into user values ('araikuma', 32, 'Seoul');
sqlite> insert into user values ('happykuma', 23, 'Seoul');
sqlite>

The name column currently has no duplicates, so create a unique index on it.

create unique index nameindex on user (name);

sqlite> create unique index nameindex on user (name);
sqlite>

The unique index has been created.

–

You can no longer insert a row whose indexed value duplicates an existing value. The following statement fails because devkuma already exists.

insert into user values ('devkuma', 18, 'Busan');

sqlite> insert into user values ('devkuma', 18, 'Busan');
Error: UNIQUE constraint failed: user.name
sqlite>

Multiple NULL values are permitted in a column covered by a unique index.

–

The address column contains duplicate values. Attempting to create a unique index on it therefore fails.

create unique index nameindex2 on user (address);

sqlite> create unique index nameindex2 on user (address);
Error: UNIQUE constraint failed: user.address
sqlite>

Relationship to PRIMARY KEY and UNIQUE Constraints

Table columns can have PRIMARY KEY constraints and UNIQUE constraints.

These constraints behave similarly to a unique index. The official SQLite documentation explains that UNIQUE and PRIMARY KEY constraints are usually implemented with a unique index, except for INTEGER PRIMARY KEY and primary keys on WITHOUT ROWID tables. It gives the following logically equivalent schemas:

SQLite Query Language: CREATE TABLE

CREATE TABLE t1(a, b UNIQUE);

CREATE TABLE t1(a, b PRIMARY KEY);

CREATE TABLE t1(a, b);
CREATE UNIQUE INDEX t1b ON t1(b);

A table can have only one PRIMARY KEY constraint, but it can have multiple UNIQUE constraints and unique indexes. Although their behavior is similar, they express different design intentions, so choose the appropriate mechanism.