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?


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

  • 1
    @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. Mar 26 '12 at 13:31
  • @ypercube: oh yeah, read the docs wrong!
    – gbn
    Mar 26 '12 at 13:32

The second and and third are equivalent.

The first is like the third, but adds a (non-unique) index in addition to the unique index. Which is unnecessary and wasteful.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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