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 / Normal Indexes or Keys / Question No: 141

How to create normal index or key in MySQL?

I want to create INDEX (KEY) in my table without any constraint. How to create index or key without constraints in MySQL? Any help please?

Answer No: 141

MySQL allows to create index without constrains like 'record uniqueness'. This type of index is also known as "non-unique index, normal index or ordinary index". To create index without constraints is as easy as unique key index or primary key index. MySQL also allows to create normal index on single column as well as multi columns (combination of columns) of the table. To create normal index (index without constraints), MySQL provides CREATE TABLE, ALTER TABLE and CREATE INDEX statements. So here, we can use them to create single column normal (without constraints) index:

Take an example to add a single column normal index while creating table:

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,
INDEX idx_fname(first_name) /* applying normal index */);

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

ALTER TABLE buyers ADD INDEX 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 normal index in an existing. Here, we will use CREATE INDEX statement:

CREATE 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-normal-index-or-key-in-MySQL How to create multi column normal index or key 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.