Usage of mysql UNIQUE Indexes

How to Use UNIQUE indexes in mysql tutorial

Syntax:
Unique key 'test' ('xref _ key', 'xref _ dbname ')
Unique key 'xref _ key' ('xref _ key', 'xref _ dbname ')

The preceding syntax checks whether the combination of xref_key and xref_dbname is a unique value. You can set a group of multiple fields.

. Test is the name of the field in the index table.
This method is suitable for solving the problem where multiple fields are mutually dependent and must meet the unique condition. Frequently

Adding or deleting the primary key of auto_increment in a data table can cause a lot of faults.

Auto_increment is not a suitable index value, so unique key can be used for processing.

Instance field values are not repeated

Create table 'test1 '(
'Id' int (11) not null auto_increment,
'Name' varchar (11) default NULL,
Primary key ('id '),
Unique key ('name ')
);

Instance

Mysql> create table Employee (
-> Id int,
-> First_name VARCHAR (15 ),
-> Last_name VARCHAR (15 ),
-> Start_date DATE,
-> End_date DATE,
-> Salary FLOAT (8, 2 ),
-> City VARCHAR (10 ),
-> Description VARCHAR (15)
-> );
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', '123', '123 ',

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', '123', '123 ',

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', '123', '123 ',

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, 'cela', 'Rice ', '123', '123 ',

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', '123', '123 ',

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', '123', '123 ',

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', '123', '123 ',

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', '123', '123 ',

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 | 1234.56 | Toronto |

Programmer |
| 2 | Alison | Mathews | 6661.78 | Vancouver |

Tester |
| 3 | James | Smith | 6544.78 | Vancouver |

Tester |
| 4 | Celia | Rice | 1982-10-24 | 2344.78 | Vancouver |

Manager |
| 5 | Robert | Black | 2334.78 | Vancouver |

Tester |
| 6 | Linda | Green | 4322.78 | New York |

Tester |
| 7 | David | Larry | 7897.78 | New York |

Manager |
| 8 | James | Cat | 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, you can view 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)

The following example shows how to delete an index:

Alter table fuinfo drop index email;