MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Indexes / Unique Key or Index / Question No: 145

How to create multi column unique key or index in MySQL?

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?

Answer No: 145

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

Related MySQL FAQs to the Above FAQ

How-to-create-unique-key-or-index-in-MySQL How to create unique key or index in MySQL?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.