Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there any different how mysql treats

UNIQUE (column1, column2),
INDEX (column1)


UNIQUE INDEX (column1, column2)


UNIQUE (column1, column2)

In the latter, I think mysql will automatically creates index for UNIQUE to avoid duplicate records. Am I right?

share|improve this question
up vote 7 down vote accepted

UNIQUE (column1, column2) implies UNIQUE INDEX (column1, column2) because the INDEX keyword is optional. So an index is created. However, the MySQL 5.5 docs show that the INDEX (or KEY) keyword is mandatory so UNIQUE (column1, column2) should give an error

INDEX (column1, column2) does not mean UNIQUE INDEX (column1, column2): it means an index that does allow duplicate (column1, column2) pairs

share|improve this answer
+1 for quoting the change in mysql 5.5! – All Mar 26 '12 at 13:11
@gbn: The linked page has this: [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY], so UNIQUE (column1, column2) does not give an error. Neither in 5.1, nor in 5.5. – ypercubeᵀᴹ Mar 26 '12 at 13:31
@ypercube: oh yeah, read the docs wrong! – gbn Mar 26 '12 at 13:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.