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
-
Someone else had the same problem, but their question was unanswered: mail-archive.com/[email protected]/msg68695.htmlrstackhouse– rstackhouse2013-12-13 21:30:36 +00:00Commented Dec 13, 2013 at 21:30
-
1It's telling you why -- because the columns that you are trying to make into a UNIQUE INDEX are not unique.BWS– BWS2013-12-13 21:32:33 +00:00Commented Dec 13, 2013 at 21:32
-
It's a pretty lousy error message. It really means that there are some repeated values for that column.rstackhouse– rstackhouse2013-12-16 14:35:36 +00:00Commented Dec 16, 2013 at 14:35
Add a comment
|
2 Answers
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
Comments
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.
