SQLite is a lightweight, serverless SQL database engine commonly used for developing mobile applications, web applications, and embedded systems. Among its various functionalities, SQLite allows developers to create indexes, including unique indexes, to enhance data retrieval speed and enforce constraints on data integrity.

A unique index ensures that all values in a specific column, or combination of columns, are distinct, preventing duplicate entries.

In this guide, we’ll walk through the basics of unique indexes in SQLite, explore when and why to use them, and provide examples of how to create them.

What is a Unique Index in SQLite?

A unique index is a special type of index that enforces uniqueness on the indexed column(s). In other words, it ensures that no two rows in the table have the same value(s) for the specified column(s). Unique indexes are helpful in maintaining data integrity, especially when you want to prevent duplicate records in your database.

For instance, if you have a column that stores email addresses, a unique index can prevent the same email address from appearing more than once in the table.

Benefits of Using Unique Indexes

• Data Integrity: Enforces unique values, ensuring data reliability.
• Improved Query Performance: Speeds up search queries on indexed columns.
• Error Prevention: Prevents accidental duplicate data entry.

Creating a Unique Index in SQLite

To create a unique index in SQLite, you can use the CREATE UNIQUE INDEX statement. This statement allows you to specify the table and column(s) on which to enforce uniqueness.

Syntax for Creating a Unique Index

CREATE UNIQUE INDEX index_name ON table_name(column_name);

• index_name: The name you want to give to the unique index.
• table_name: The name of the table where the unique index will be applied.
• column_name: The name of the column(s) that the unique index will enforce uniqueness on.

If you want the index to cover multiple columns, list them separated by commas within the parentheses.

Example: Creating a Unique Index on a Single Column

Let’s create a unique index on a users table to ensure that email addresses are unique.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_unique_email ON users(email);

In this example:

• We create a table named users with three columns: id, username, and email.
• The CREATE UNIQUE INDEX statement creates a unique index named idx_unique_email on the email column of the users table, ensuring that each email address is unique.

Now, if we attempt to insert duplicate email addresses into the users table, SQLite will return an error.

Testing the Unique Index

Let’s test this unique index by trying to insert two rows with the same email.

INSERT INTO users (username, email) VALUES ('user1', '[email protected]');
INSERT INTO users (username, email) VALUES ('user2', '[email protected]'); -- This will fail

The first INSERT statement will succeeded, but the second INSERT will fail with an error message that looks something like this:

Runtime error: UNIQUE constraint failed: users.email (19)

Let’s check the table:

SELECT * FROM users;

Output:

+----+----------+-------------------+
| id | username |       email       |
+----+----------+-------------------+
| 1  | user1    | [email protected] |
+----+----------+-------------------+

As expected, only one row was inserted.

Example: Creating a Unique Index on Multiple Columns

You can also create a unique index on multiple columns. This is useful when the uniqueness constraint should apply to a combination of columns rather than a single column.

For example, let’s say you have a contacts table where you want to ensure that the combination of first_name and last_name is unique:

CREATE TABLE contacts (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    phone_number TEXT
);

CREATE UNIQUE INDEX idx_unique_full_name ON contacts(first_name, last_name);

Here:

• We define a contacts table with id, first_name, last_name, and phone_number columns.
• We create a unique index named idx_unique_full_name on the combination of first_name and last_name.

This unique index will allow multiple rows with the same first_name or the same last_name, but it will prevent duplicate entries where both first_name and last_name match.

Testing the Multi-Column Unique Index

To test this, we’ll try to insert rows with duplicate names:

INSERT INTO contacts (first_name, last_name, phone_number) VALUES ('Ed', 'White', '123-456-7890');
INSERT INTO contacts (first_name, last_name, phone_number) VALUES ('Amy', 'White', '321-654-0987');
INSERT INTO contacts (first_name, last_name, phone_number) VALUES ('Ed', 'Teach', '123-456-7890');
INSERT INTO contacts (first_name, last_name, phone_number) VALUES ('Ed', 'Teach', '098-765-4321'); -- This will fail

The first three INSERT statements will work, but the fourth will result in an error due to the duplicate combination of first_name and last_name.

Here’s the output:

Runtime error: UNIQUE constraint failed: contacts.first_name, contacts.last_name (19)

As expected, an error.

Let’s check the table:

SELECT * FROM contacts;

Output:

+----+------------+-----------+--------------+
| id | first_name | last_name | phone_number |
+----+------------+-----------+--------------+
| 1  | Ed         | White     | 123-456-7890 |
| 2  | Amy        | White     | 321-654-0987 |
| 3  | Ed         | Teach     | 123-456-7890 |
+----+------------+-----------+--------------+

As expected, only the first three rows were inserted.

Deleting a Unique Index

To delete a unique index, use the DROP INDEX statement:

DROP INDEX idx_unique_email;

This command removes the unique index named idx_unique_email from the database. Note that deleting an index does not delete any data from the table itself.

Modifying a Unique Index

SQLite does not support directly modifying an existing unique index. If you need to change an index, you must first drop the existing index and then create a new one with the desired changes.