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.
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;