Why do I get a "indexed columns are not unique" error when trying to add a UNIQUE INDEX to a SQLite Table?

Question

When attempting to create an unique index with the following syntax:

CREATE UNIQUE INDEX Table_Index ON Table (CharColumn, IntColumn)

Why do I receive the error:

indexed columns are not unique

Answer 1

You get the error "indexed columns are not unique" because the indexed columns are not unique, that is, there are some duplicate records.

Use a query like this to find out which records:

SELECT CharColumn,
       IntColumn,
       COUNT(*) AS Count
FROM MyTable
GROUP BY CharColumn,
         IntColumn
HAVING Count > 1

Answer 2

If your table have already containing rows, then probably you have a 2 or more rows with the same values indicated in your tables unique index (CharColumn, IntColumn)

e.g. row CharColumn IntColumn 10 ABC 1 21 ABC 1

A unique index means no more than 1 rows should contains the value.