1

I have this table

CREATE TABLE `country` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to add a unique index so that the table cannot contain duplicate countries.

Edit: To clarify, I want the name column to have unique values, but I also want to optimize it for queries like SELECT name FROM country WHERE name = ?.

If my table were to allow duplicate countries, I'd normally achieve this with an INDEX key..

Do I need two indices on the name column (one INDEX and one UNIQUE) or will a single UNIQUE index do the job?

kYuZz
  • 1,572
  • 4
  • 14
  • 25
  • possible duplicate of [Differences between INDEX, PRIMARY, UNIQUE, FULLTEXT in MySQL?](http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-in-mysql) – Jon Jul 17 '14 at 19:10

1 Answers1

1

If you want the database to enforce that the column name contains UNIQUE values, you'd need a UNIQUE KEY in place of KEY.

If you want the database to allow duplicate values for name, then the KEY you have is just fine.

spencer7593
  • 106,611
  • 15
  • 112
  • 140