Home / Indexes / Normal Indexes or Keys / Question No: 141

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?

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 */