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: 144

How to create unique key or index in MySQL?

I intends to use indexes in MySQL to make SELECT queries faster. I don't know that how to create indexes in MySQL as I am unable to use phpMyAdmin. Can you explain, how to create unique key or index in MySQL without using phpMyAdmin etc?

Answer No: 144

MySQL allows to create unique key or index on single column as well as multi columns (combination of columns) of the table. To create unique index, MySQL provides CREATE TABLE, ALTER TABLE and CREATE UNIQUE INDEX statements. So here, we can use them to create single column unique index:

Take two examples to add a single column unique index while creating table:
CREATE TABLE buyers ( 
buyer_id INT UNSIGNED NOT NULL,
first_name CHAR(19) NOT NULL UNIQUE /* applying unique key index */,
last_name CHAR(19) NOT NULL,
age SMALLINT NOT NULL,
post_code SMALLINT NOT NULL
);

or

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_fname(first_name) /* applying unique index */
);

Take an example to add single column unique index in an existing table. We can do this with ALTER TABLE statement:

ALTER TABLE buyers ADD UNIQUE indx_fname (first_name); 
/* Note: here "indx_fname" is the name of index being created.
It is optional, so you can omit this if you don't require */

Another example to add single column unique index in an existing. Here, we will use CREATE UNIQUE INDEX statement:

CREATE UNIQUE INDEX idx_fname ON buyers(first_name);
/* Note: here "indx_fname" is the name of index being created.
It is optional, so you can omit this if you don't require */

Related MySQL FAQs to the Above FAQ

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

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

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

© 2021  www.mysqlfaqs.net
All rights reserved.