Home / Indexes / Unique Key or Index / Question No: 145

I am newbie to MySQL indexes. While creating multi column unique index in MySQL I am getting some problems. Can you explain, how to create multi column UNIQUE KEY or INDEX in MySQL?

To create multi columns unique key or index is simple as non-unique index or normal index and primary key index. Since MySQL allows to create unique key or index on single column as well as on multi columns (combination of columns) of the table, you can create multi columns unique index using CREATE TABLE, ALTER TABLE and CREATE UNIQUE INDEX statements provided by the MySQL:

You can create multi column Unique Index while creating your table. The following is an example to create multi column Unique Index using CREATE TABLE statement:

CREATE TABLE buyers ( 
 buyer_id INT UNSIGNED NOT NULL, 
 first_name CHAR(19) NOT NULL, 
 last_name CHAR(19) NOT NULL, 
 age SMALLINT NOT NULL, 
 post_code SMALLINT NOT NULL, 
 UNIQUE idx_flname_age (first_name,last_name,age)
);

You can create multi column Unique Index after creating your table or when you decide to add it later. In this case, you will have to alter your table. The following is an example to create multi column Unique Index using ALTER TABLE statement:

Syntax to alter the table to add an unique index:

ALTER TABLE tablename ADD UNIQUE name_of_index[optional] (columns_to_index);

Add multi column unique index or key altering the "buyers" table created above:

ALTER TABLE buyers ADD UNIQUE idx_flname_age(first_name,last_name,age);

Also, you can create multi column Unique Index without altering your table. This is possible through CREATE UNIQUE INDEX statement. The following is an example to create multi column Unique Index using CREATE UNIQUE INDEX statement:

Syntax to create multi column unique index without altering table:

CREATE UNIQUE INDEX name_of_index[optional] ON tablename (columns_to_index);

Add multi column unique index in the "buyers" created above using CREATE UNIQUE INDEX statement:

CREATE UNIQUE INDEX idx_flname_age ON buyers(first_name,last_name,age);