A unique index
In MySQL, the UNIQUE keyword is used to create a UNIQUE index. A normal index allows duplicates in indexed columns, but a unique index does not allow duplicates in indexed columns. The syntax for defining a unique index is as follows:
CREATE UNIQUE INDEX indexName ON tableName(columnNme(length));
Copy the code
Note:
- If a column in a table does not allow duplicate values, create a unique index for it. For example, mobile phone number, ID number and so on;
- Unique indexes can be scoped on one or more columns, and these columns or combinations of columns must be unique;
- Multiple unique indexes can be created on the same table;
- Columns with unique indexes allow null values;
- Generating duplicate values for unique indexed columns using INSERT and UPDATE will result in an error.
For example, create a unique index for the phone column in the Student table
create unique idx_phone on student(phone);
Copy the code