Being able to ensure uniqueness in database tables is critical for maintaining data integrity. SQLite allows us to create unique indexes in order to enforce uniqueness, and it provides us with tools to check the unique indexes associated with a table.

This article outlines how to use the SQLite PRAGMA index_list() command to check a table for unique indexes.

The PRAGMA index_list() Command

The PRAGMA index_list() command returns information about all indexes on a given table, including whether they enforce uniqueness.

Syntax

PRAGMA index_list('table_name');

Example

Consider the following table and indexes:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    firstname TEXT,
    lastname TEXT,
    email TEXT
);

CREATE UNIQUE INDEX idx_unique_email ON users(email);
CREATE INDEX idx_full_name ON users(firstname, lastname);

Here:

• The username column is defined as UNIQUE (which will give it a unique index).
• Regarding the email column, we created a unique index on this column by using the CREATE UNIQUE INDEX statement.
• We also created an index on the firstname and lastname columns, but it’s not unique.

Now let’s check for unique indexes:

PRAGMA index_list('users');

Output:

+-----+--------------------------+--------+--------+---------+
| seq |           name           | unique | origin | partial |
+-----+--------------------------+--------+--------+---------+
| 0   | idx_full_name            | 0      | c      | 0       |
| 1   | idx_unique_email         | 1      | c      | 0       |
| 2   | sqlite_autoindex_users_1 | 1      | u      | 0       |
+-----+--------------------------+--------+--------+---------+

The unique column indicates whether an index enforces uniqueness (1 for yes, 0 for no).

As expected, two columns have unique indexes.

• The idx_full_name index is the non-unique index. The 0 tells us that it’s not unique.
• The idx_unique_email index is the one we created with the CREATE UNIQUE INDEX statement. The 1 in the unique column tells us that it’s unique. The c in the origin column tells us that the index was created explicitly with a CREATE UNIQUE INDEX or statement (or CREATE INDEX in the case of the non-unique index).
• The sqlite_autoindex_users_1 entry is the automatic unique index created by the UNIQUE keyword for the username column. The 1 in the unique column tells us that it’s unique. The u in the origin column tells us that it was created by a UNIQUE constraint.

A Quick Note on the sqlite_schema Table

You may be tempted to query the sqlite_schema table (or any of its synonyms: sqlite_master, sqlite_temp_schema, sqlite_temp_master) for unique indexes. In particular, you might decide to narrow the results to just indexes, and then look through the sql column on all indexes for any code that contains UNIQUE. But you should be aware that this option will only work if you explicitly created the index (e.g., with the CREATE INDEX or CREATE UNIQUE INDEX statements).

The sqlite_schema table includes a sql column that you can use to see the CREATE UNIQUE INDEX statement (or whatever other statement was used to create an object). But when you create a unique index by using the UNIQUE keyword directly in the column (like we did with the username column), the sql column returns null.

For example, if we run the following query:

SELECT 
    name,
    sql
FROM sqlite_schema 
WHERE type = 'index' 
  AND tbl_name = 'users';

We get this:

+--------------------------+----------------------------------------------------------+
|           name           |                           sql                            |
+--------------------------+----------------------------------------------------------+
| sqlite_autoindex_users_1 | null                                                     |
| idx_unique_email         | CREATE UNIQUE INDEX idx_unique_email ON users(email)     |
| idx_full_name            | CREATE INDEX idx_full_name ON users(firstname, lastname) |
+--------------------------+----------------------------------------------------------+

We can see that the sqlite_autoindex_users_1 index has got null in the sql column. That’s because it’s an internal index created automatically by the UNIQUE keyword.

If you must use this table, then another option is to remove the index filter and return all rows for the table:

SELECT 
    name,
    sql
FROM sqlite_schema 
WHERE tbl_name = 'users';

Output:

+--------------------------+----------------------------------------------------------+
|           name           |                           sql                            |
+--------------------------+----------------------------------------------------------+
| users                    | CREATE TABLE users (                                     |
|                          |     id INTEGER PRIMARY KEY,                              |
|                          |     username TEXT UNIQUE,                                |
|                          |     firstname TEXT,                                      |
|                          |     lastname TEXT,                                       |
|                          |     email TEXT                                           |
|                          | )                                                        |
+--------------------------+----------------------------------------------------------+
| sqlite_autoindex_users_1 | null                                                     |
+--------------------------+----------------------------------------------------------+
| idx_unique_email         | CREATE UNIQUE INDEX idx_unique_email ON users(email)     |
+--------------------------+----------------------------------------------------------+
| idx_full_name            | CREATE INDEX idx_full_name ON users(firstname, lastname) |
+--------------------------+----------------------------------------------------------+

This includes a row for the whole table. We can now see the username column with its UNIQUE keyword.