How to use MySQL unique index

Source: Internet
Author: User
Tags mysql tutorial

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.