SQLite: Unique Constraints

This SQLite post explains how to create, add, and drop unique constraints in SQLite with syntax and examples.

What is a unique constraint in SQLite?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

What is the difference between a unique constraint and a primary key?

Create unique Contraint

A unique constraint can be created when you execute a CREATE TABLE statement in SQLite.

Syntax

The syntax for creating a unique constraint using a CREATE TABLE statement in SQLite is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

table_name

The name of the table that you wish to create.

column1, column2

The columns that you wish to create in the table.

constraint_name

The name of the unique constraint.

uc_col1, uc_col2, ... uc_col_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to create a unique constraint in SQLite.

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE,
  CONSTRAINT name_unique UNIQUE (last_name, first_name)
);

In this example, we've created a unique constraint on the employees table called name_unique. It consists of the last_name and first_name fields.

Add Unique Constraint

If your table already exists and you wish to add a unique constraint later, you can not use the ALTER TABLE statement to add the unique constraint. Instead, you must create a new table with the unique constraint and copy the data into this new table.

Syntax

The syntax to add a unique constraint to a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table_name RENAME TO old_table;

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);

INSERT INTO table_name SELECT * FROM old_table;

COMMIT;

PRAGMA foreign_keys=on;

table_name

The name of the table to modify. This is the table that you wish to add a unique constraint to.

old_table

The name of the original table. It will be left behind after you have created the new table with the unique constraint added.

constraint_name

The name of the unique constraint.

uc_col1, uc_col2, ... uc_col_n

The columns that make up the unique constraint.

Example

Let's look at an example of how to add a unique constraint to an existing table in SQLite. So say, we already have a products table with the following definition:

CREATE TABLE products
( product_id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_name VARCHAR NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0
);

And we wanted to add a unique constraint to the products table that consists of the product_name. We could run the following commands:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE products RENAME TO old_products;

CREATE TABLE products
( product_id INTEGER PRIMARY KEY AUTOINCREMENT,
  product_name VARCHAR NOT NULL,
  quantity INTEGER NOT NULL DEFAULT 0,
  CONSTRAINT product_name_unique UNIQUE (product_name)
);

INSERT INTO products SELECT * FROM old_products;

COMMIT;

PRAGMA foreign_keys=on;

In this example, we've created a unique constraint on the products table called product_name_unique which consists of the product_name column. The original table will still exist in the database called old_products. You can drop the old_productstable once you have verified that your products table and data are as expected.

DROP TABLE old_products;

Drop Unique Constraint

In SQLite, you can not use the ALTER TABLE statement to drop a unique constraint. Instead, you must create a new table with the unique constraint removed and copy the data into this new table.

Syntax

The syntax to drop a unique constraint from a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table_name RENAME TO old_table;

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table_name SELECT * FROM old_table;

COMMIT;

PRAGMA foreign_keys=on;

table_name

The name of the table to modify. This is the table that you wish to remove the unique constraint from.

old_table

The name of the original table. It will be left behind after you have created the new table with the unique constraint removed.

Example

Let's look at an example of how to remove a unique constraint from an existing table in SQLite. So say, we already have a suppliers table with the following definition:

CREATE TABLE suppliers
( supplier_id INTEGER PRIMARY KEY AUTOINCREMENT,
  supplier_name VARCHAR NOT NULL,
  address VARCHAR,
  city VARCHAR,
  CONSTRAINT supplier_name_unique UNIQUE (supplier_name)
);

And we wanted to drop the unique constraint from the suppliers table. We could run the following commands:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE suppliers RENAME TO old_suppliers;

CREATE TABLE suppliers
( supplier_id INTEGER PRIMARY KEY AUTOINCREMENT,
  supplier_name VARCHAR NOT NULL,
  address VARCHAR,
  city VARCHAR
);

INSERT INTO suppliers SELECT * FROM old_suppliers;

COMMIT;

PRAGMA foreign_keys=on;

In this example, we've dropped the unique constraint on the existing suppliers table. The original table will still exist in the database called old_suppliers. You can drop the old_suppliers table once you have verified that your suppliers table and data are as expected.

DROP TABLE old_suppliers;