CreateUNIQUEINDEXid_fname_lnameonemployee(empid,first_name,last_name);QueryOK,0rowsaffected(0.41sec )Records:0Duplicates:0Warnings:0m"/> CreateUNIQUEINDEXid_fname_lnameonemployee(empid,first_name,last_name);QueryOK,0rowsaffected(0.41sec )Records:0Duplicates:0Warnings:0m">
Home >Database >Mysql Tutorial >How do we create a multi-column UNIQUE index?
In order to create a multi-column UNIQUE index, we need to specify the index name on multiple columns. The following example will create a multi-column index named "id_fname_lname" on the "empid", "first_name", and "last_name" columns of the "employee" table -
mysql> Create UNIQUE INDEX id_fname_lname on employee(empid,first_name,last_name); Query OK, 0 rows affected (0.41 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe employee; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | empid | int(11) | YES | MUL | NULL | | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(20) | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.12 sec)
From the result set of the above query, we can see Multiple indexes are defined on the table. Forgetting details about indexes, we can run the following query -
mysql> Show index from employee\G *************************** 1. row *************************** Table: employee Non_unique: 0 Key_name: id_fname_lname Seq_in_index: 1 Column_name: empid Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 2. row *************************** Table: employee Non_unique: 0 Key_name: id_fname_lname Seq_in_index: 2 Column_name: first_name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: *************************** 3. row *************************** Table: employee Non_unique: 0 Key_name: id_fname_lname Seq_in_index: 3 Column_name: last_name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: 3 rows in set (0.00 sec)
As you can see from the above result set, the value in the 'key_name' field is the same as we have created on all the columns of the table Multi-column index.
The above is the detailed content of How do we create a multi-column UNIQUE index?. For more information, please follow other related articles on the PHP Chinese website!