SQLite | Tables | UNIQUE Constraint

Tags:

• SQLite
• Database

A UNIQUE constraint prevents duplicate values from being stored in a column. This article explains how to use UNIQUE constraints.

What Is a UNIQUE Constraint?

When a column has a UNIQUE constraint, it cannot contain duplicate values. The syntax is as follows.

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

Create the following table. The id column has a UNIQUE constraint.

create table user (id integer unique, name text);

sqlite> create table user (id integer unique, name text);
sqlite> 

Insert several rows.

insert into user values (1, 'devkuma');
insert into user values (2, 'kimkc');
insert into user values (4, 'araikuma');

sqlite> insert into user values (1, 'devkuma');
sqlite> insert into user values (2, 'kimkc');
sqlite> insert into user values (4, 'araikuma');
sqlite> 

Next, insert a row with an id that already exists. SQLite reports Error: UNIQUE constraint failed: user.id.

insert into user values (2, 'happykuma');

sqlite> insert into user values (2, 'happykuma');
Error: UNIQUE constraint failed: user.id
sqlite>

The constraint prevents duplicate values from being stored in the column.

However, a UNIQUE column may contain multiple NULL values.

insert into user values (null, 'mykuma');
insert into user values (null, 'yourkuma');

sqlite> insert into user values (null, 'mykuma');
sqlite> insert into user values (null, 'yourkuma');
sqlite> 

To disallow NULL, also add a NOT NULL constraint.

Applying UNIQUE to a Combination of Columns

A UNIQUE constraint can also apply to a combination of columns. The syntax is as follows.

CREATE TABLE table_name (column1, column2, ... , UNIQUE (column1, column2, ...));

With a two-column constraint, each individual column may contain duplicates, but the same pair of values cannot appear more than once.

Create a table with a UNIQUE constraint on the pair of no and unit columns.

create table employee (no integer, unit text, name text, unique (no, unit));

sqlite> create table employee (no integer, unit text, name text, unique (no, unit));
sqlite> 

Insert several rows.

insert into employee values (1, 'Sales', 'devkuma');
insert into employee values (4, 'Dev', 'kimkc');
insert into employee values (2, 'Office', 'araikuma');

sqlite> insert into employee values (1, 'Sales', 'devkuma');
sqlite> insert into employee values (4, 'Dev', 'kimkc');
sqlite> insert into employee values (2, 'Office', 'araikuma');
sqlite> 

Now insert rows where only no or only unit matches an existing row. These inserts succeed because the complete pair is different.

insert into employee values (4, 'Design', 'mykuma');
insert into employee values (7, 'Sales', 'yourkuma');

sqlite> insert into employee values (4, 'Design', 'mykuma');
sqlite> insert into employee values (7, 'Sales', 'yourkuma');
sqlite> 

Inserting a row whose no and unit pair already exists produces Error: UNIQUE constraint failed: employee.no, employee.unit.

insert into employee values (2, 'Office', 'blackkuma');

sqlite> insert into employee values (2, 'Office', 'blackkuma');
Error: UNIQUE constraint failed: employee.no, employee.unit
sqlite> 

Thus, a UNIQUE constraint can enforce uniqueness on either one column or a combination of columns.