A simple tutorial on unique indexes in MySQL

mysql only index UNIQUE1 is used for non-duplicating data fields. We often set id to only index UNIQUE in data tables. Let me show you how to use the only index UNIQUE in mysql.
The sole index is not created to improve access speed, but to avoid duplication of data. Only one index may have multiple values but only one index column must have a value of one. The value of the index column is allowed to have a null value. If you are sure that a data column will contain only values that are different from one another, you should use the UNIQUE keyword when creating indexes for the data column.

Define it as a unique index.

Set directly when creating the table:

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`stu_id`),
UNIQUE KEY `UK_student_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

Create only 1 index:

create unique index UK_student_name on student (name);

Add constraints after the table is built:

alter table student add constraint uk_student_name unique (name);

If you do not need only one index, you can do so

mysql> ALTER TABLE student DROP INDEX name; 

Query OK, 0 rows affected (0.85 sec)

If you want to increase the index

alter table user add unique index(user_id,user_name);