How do I find out if a SQLite index is unique? (With SQL)

Question

I want to find out, with an SQL query, whether an index is UNIQUE or not. I'm using SQLite 3.

I have tried two approaches:

SELECT * FROM sqlite_master WHERE name = 'sqlite_autoindex_user_1'

This returns information about the index ("type", "name", "tbl_name", "rootpage" and "sql"). Note that the sql column is empty when the index is automatically created by SQLite.

PRAGMA index_info(sqlite_autoindex_user_1);

This returns the columns in the index ("seqno", "cid" and "name").

Any other suggestions?

Edit: The above example is for an auto-generated index, but my question is about indexes in general. For example, I can create an index with "CREATE UNIQUE INDEX index1 ON visit (user, date)". It seems no SQL command will show if my new index is UNIQUE or not.

Answer 1

PRAGMA INDEX_LIST('table_name');

Returns a table with 3 columns:

1. seq Unique numeric ID of index
2. name Name of the index
3. unique Uniqueness flag (nonzero if UNIQUE index.)

Edit

Since SQLite 3.16.0 you can also use table-valued pragma functions which have the advantage that you can JOIN them to search for a specific table and column. See @mike-scotty's answer.