Home / Indexes / Primary Key Indexes / Question No: 147

I want to know methods to create PRIMARY KEY INDEX. How to create primary key indexes in MySQL?

The primary keys are almost always added when creating the table. However, MySQL provides more than one SQL statements to create primary key indexes. You can use them as per your requirement.

Syntax to create primary key index when creating a table

CREATE TABLE tablename ( [...], PRIMARY KEY (columns_to_index) );

Take an example:

CREATE TABLE buyers ( 
 buyer_id INT NOT NULL AUTO_INCREMENT, 
 first_name CHAR(19) NOT NULL, 
 last_name CHAR(19) NOT NULL, 
 age SMALLINT NOT NULL, 
 postal_code SMALLINT NOT NULL, 
 PRIMARY KEY (buyer_id) 
);

It can also be added by altering the table like

ALTER TABLE tablename ADD PRIMARY KEY (columns_to_index);

Take an example:

ALTER TABLE buyers ADD PRIMARY KEY (buyer_id);

Note: only one primary key can be added in a table.