How can we apply the PRIMARY KEY constraint to the field of an existing MySQL table?

We can apply the PRIMARY KEY constraint to a column of an existing MySQL table with the help of ALTER TABLE statement. 

Syntax

ALTER TABLE table_name MODIFY colum_name datatype PRIMARY KEY;
                 OR
ALTER TABLE table_name ADD PRIMARY KEY (colum_name); 

Suppose we have the following table named ‘Player’ and we want to add the PRIMARY KEY constraint to the column ‘ID’ then it can be done with the help of ALTER TABLE command as follows −

mysql> DESCRIBE Player;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    |  int(11)    | YES  |     | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

2 rows in set (0.04 sec)
mysql> ALTER TABLE Player MODIFY ID INT PRIMARY KEY;
Query OK, 0 rows affected (0.22 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESCRIBE Player;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    |  int(11)    | NO   | PRI | NULL    |       |
| Name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

2 rows in set (0.04 sec) 

From the above result set, it can be observed that MySQL added a PRIMARY KEY constraint to the filed ‘ID’. We can add a PRIMARY KEY constraint with the following query as well −

 Alter table Player ADD PRIMARY KEY(ID);

Vikyath Ram

Updated on 19-Jun-2020 11:52:38

• Related Questions & Answers
• How can we apply UNIQUE constraint to the field of an existing MySQL table?
• How can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
• How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
• How can we remove composite PRIMARY KEY constraint applied on multiple columns of an existing MySQL table?
• How can we set PRIMARY KEY on multiple columns of an existing MySQL table?
• How can we apply a NOT NULL constraint to a column of an existing MySQL table?
• How can we remove FOREIGN KEY constraint from a column of an existing MySQL table?
• What is MySQL UNIQUE constraint and how can we apply it to the field of a table?
• Which statement, other than ALTER TABLE statement, can be used to apply UNIQUE constraint to the field of an existing MySQL table?
• Can we remove a primary key from MySQL table?
• How can we remove NOT NULL constraint from a column of an existing MySQL table?
• Create Primary Key on an existing table in PostgreSQL?
• How can we set PRIMARY KEY on multiple columns of a MySQL table?
• How can I define a column of a MySQL table PRIMARY KEY without using the PRIMARY KEY keyword?
• How to reset the primary key of a table in mysql?