Set existing column as Primary Key in MySQL?

MySQLMySQLi Database

You can set primary key on an existing column in MySQL with the help of alter command.

The syntax is as follows to add primary key to an existing column.

ALTER TABLE yourTableName ADD PRIMARY KEY(yourColumnName);

To set existing column as primary key, let us first create a table. The query to create a table −

mysql> create table AddingPrimaryKeyDemo
   −> (
      −> UniversityId int,
      −> UniversityName varchar(200)
   −> );
Query OK, 0 rows affected (1.16 sec)

Look at the above query, I haven’t added primary key. Let us check the same with the help of DESC command. The query is as follows −

mysql> desc AddingPrimaryKeyDemo;

The following is the output −

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| UniversityId   | int(11)      | YES |      | NULL    |       |
| UniversityName | varchar(200) | YES |      | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
2 rows in set (0.09 sec)

Now you can set existing column as primary key with the help of the syntax discussed in the beginning. The query to add primary key is as follows −

mysql> alter table AddingPrimaryKeyDemo add primary key(UniversityId);
Query OK, 0 rows affected (1.48 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now I have added primary to existing column ‘UniversityId’. The following command will give a result that would check the existence of primary key on column ‘UniversityId’.

mysql> desc AddingPrimaryKeyDemo;

The following is the output −

+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| UniversityId   | int(11)      | NO   | PRI | NULL    |       |
| UniversityName | varchar(200) | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

We can see “PRI” above, therefore Primary Key successfully added.

raja
Published on 10-Jan-2019 06:39:26
Advertisements