Syntax error How can we create multicolumn UNIQUE indexes?

How can we create multicolumn UNIQUE indexes?

For creating multicolumn UNIQUE indexes we need to specify an index name on more than one column. Following example will create a multicolumn index named ‘id_fname_lname’ on the columns ‘empid’, ’first_name’, ’last_name’ of ‘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 above query, we can see that a multiple index is defined on the table. Forgetting the details about the 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)

We can observe from the above result set that the value in the ‘key_name’ filed is same because we have created the multicolumn index on all the columns of the table.

Updated on: 2020-01-28T06:26:06+05:30

116 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements