Alter Table Add Primary Key MySQL Example via Command Line

This is an article which is created to change a table exist in MySQL Database Server. The change which is going to be achieved is by adding a primary key to the table. This is actually happens when a created table didn’t have a field acts as a primary key. Adding a new field and then assign it as a primary key will make itself as an attempt to change or to alter the structure of a table.

The scenario for implementing on adding a new primary key on a table which is actually just create a simple table without any primary key and then altering it by adding a new primary key. So, these are the steps done to show how to add primary key field in a table :

1. Create a new table. Below is actually the syntax which can be executed in MySQL console’s command line to achieve it :

mysql> create table table_test(name varchar(255) not null);
Query OK, 0 rows affected (0,11 sec)
mysql> desc table_test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0,01 sec)
mysql> 

As shown in the above output, there is a table named ‘table_test’ without any primary key field.

2. Alter the already created table by adding a new field for primary key. Below is the actual command which can be executed :

alter table table_name add primary key(field_name)

Beware that the above syntax execution must be executed with a proper and correct syntax, if it not, it will generated a following errors as shown below :

mysql> alter table table_test add primary_key(id_table);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(id_table)' at line 1
mysql>

So, execute it in MySQL command console as shown below :

mysql> alter table table_test add primary key(id_table);
Query OK, 0 rows affected (0,18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>

After successfully executed the above command, below is how to describe the table :

mysql> desc table_test;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| name      | varchar(255) | NO   |     | NULL    |       |
| id_table  | int(11)      | NO   | PRI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
2 rows in set (0,00 sec)
mysql> 

The above output exactly described that the adding process of a primary key field has already succeed.