How to use MySQL unique index

MySQL tutorial How to use the unique index

Grammar:
UNIQUE KEY ' test ' (' Xref_key ', ' xref_dbname ')
UNIQUE KEY ' Xref_key ' (' Xref_key ', ' xref_dbname ')

The above syntax checks whether the combination of Xref_key and Xref_dbname is a unique value and can be set for multiple fields

Collection Where test is the name of the field in the Index table.
This method is suitable for situations where multiple fields depend on each other and must satisfy uniqueness. In regular

Adding or removing a auto_increment primary key in a table creates a large number of faults, and this kind of variable table of data

Using Auto_increment is not an appropriate index value, so you can take the unique key to handle it.

Instance field values are not duplicated

CREATE TABLE ' test1 ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (one) default NULL,
PRIMARY KEY (' id '),
UNIQUE KEY (' name ')
);

Instance

Mysql> CREATE TABLE Employee (
   ->     id             int,
   ->     first_name     VARCHAR,
   ->     last_name      VARCHAR,
   ->     start_date    date,
   ->     end_date      date,
    ->     salary        FLOAT (8,2),
    ->     city          VARCHAR (Ten),
   ->     description   VARCHAR (a)
   ->);
Query OK, 0 rows affected (0.03 sec)

Mysql>
Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, End_date,

Salary, city, Description)
-> values (1, ' Jason ', ' Martin ', ' 19960725 ', ' 20060725 ',

1234.56, ' Toronto ', ' Programmer ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, End_date,

Salary, city, Description)
-> values (2, ' Alison ', ' Mathews ', ' 19760321 ', ' 19860221 ',

6661.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, End_date,

Salary, city, Description)
-> values (3, ' James ', ' Smith ', ' 19781212 ', ' 19900315 ',

6544.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, End_date,

Salary, city, Description)
-> VALUES (4, ' Celia ', ' Rice ', ' 19821024 ', ' 19990421 ',

2344.78, ' Vancouver ', ' Manager ';
Query OK, 1 row affected (0.01 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, End_date,

Salary, city, Description)
-> values (5, ' Robert ', ' black ', ' 19840115 ', ' 19980808 ',

2334.78, ' Vancouver ', ' Tester ');
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, End_date,

Salary, city, Description)
-> VALUES (6, ' Linda ', ' Green ', ' 19870730 ', ' 19960104 ',

4322.78, ' New York ', ' Tester ';
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, End_date,

Salary, city, Description)
-> VALUES (7, ' David ', ' Larry ', ' 19901231 ', ' 19980212 ',

7897.78, ' New York ', ' Manager ';
Query OK, 1 row Affected (0.00 sec)

Mysql>
mysql> INSERT INTO Employee (Id,first_name, last_name, start_date, End_date,

Salary, city, Description)
-> VALUES (8, ' James ', ' Cat ', ' 19960917 ', ' 20020415 ',

1232.78, ' Vancouver ', ' Tester ');
Query OK, 1 row affected (0.02 sec)

Mysql>
Mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------

+-------------+
| ID | first_name | last_name | start_date | end_date | Salary | City |

Description |
+------+------------+-----------+------------+------------+---------+-----------

+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto |

Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver |

Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver |

Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver |

Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver |

Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York |

Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York |

Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver |

Tester |
+------+------------+-----------+------------+------------+---------+-----------

+-------------+
8 rows in Set (0.00 sec)

Mysql>
Mysql>
Mysql>
mysql> ALTER TABLE Employee ADD UNIQUE address_index (city);
ERROR 1062 (23000): Duplicate entry ' Vancouver ' for key 1
mysql> ALTER TABLE Employee ADD UNIQUE lastn_index (last_name);
Query OK, 8 rows Affected (0.02 sec)
Records:8 duplicates:0 warnings:0

Mysql>
mysql> desc Employee;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| ID | Int (11) |     YES | |       NULL | |
| first_name | varchar (15) |     YES | |       NULL | |
| last_name | varchar (15) | YES | UNI |       NULL | |
| start_date | Date |     YES | |       NULL | |
| end_date | Date |     YES | |       NULL | |
| Salary | Float (8,2) |     YES | |       NULL | |
| City | varchar (10) |     YES | |       NULL | |
| Description | varchar (15) |     YES | |       NULL | |
+-------------+-------------+------+-----+---------+-------+
8 rows in Set (0.03 sec)

If you want to add a unique index to two fields in the same table, look at the instance

Mysql>
mysql> CREATE TABLE myTable
-> (
-> OrderID SMALLINT UNSIGNED not NULL,
-> modelid SMALLINT UNSIGNED not NULL,
-> Description VARCHAR (40),
-> PRIMARY KEY (OrderID),
-> UNIQUE (OrderID, ModelID)
->);
Query OK, 0 rows affected (0.05 sec)

Mysql>
mysql> desc myTable;
+-------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| OrderID | smallint (5) unsigned | NO |         PRI |       | |
| ModelID | smallint (5) unsigned |     NO |         |       | |
| Description | varchar (40) |     YES | |       NULL | |
+-------------+----------------------+------+-----+---------+-------+
3 Rows in Set (0.02 sec)

Below if I want to delete the index how to do, the following example

ALTER TABLE fuinfo DROP INDEX email;