Alter Table Add Primary Key MySQL Example via Command Line

Posted on

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.

2 thoughts on “Alter Table Add Primary Key MySQL Example via Command Line

Leave a Reply