Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Set existing column as Primary Key in MySQL?
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.
9K+ Views
