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

<p>I have tried two approaches:</p>

<pre><code>SELECT * FROM sqlite_master WHERE name = 'sqlite_autoindex_user_1'
</code></pre>

<p>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.</p>

<pre><code>PRAGMA index_info(sqlite_autoindex_user_1);
</code></pre>

<p>This returns the columns in the index ("seqno", "cid" and "name").</p>

<p>Any other suggestions?</p>

<p><strong>Edit:</strong> 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.</p>

you can programmatically build a select statement to see if any tuples point to more than one row. If you get back three columns, foo, bar and baz, create the following query
```
```
select count(*) from t
group by foo, bar, baz
having count(*) > 1
```
```
If that returns any rows, your index is not unique, since more than one row maps to the given tuple. If sqlite3 supports derived tables (I've yet to have the need, so I don't know off-hand), you can make this even more succinct:
```
```
select count(*) from (
    select count(*) from t
    group by foo, bar, baz
    having count(*) > 1
)
```
```
This will return a single row result set, denoting the number of duplicate tuple sets. If positive, your index is not unique.