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
ALTER TABLE to add a composite primary key in MySQL?
To add composite primary key, use the ALTER command. Let us first create a demo table
The query to create a table.
mysql> create table CompositePrimaryKey -> ( -> Id int, -> StudentName varchar(100), -> Age int -> ); Query OK, 0 rows affected (0.56 sec)
Haven’t added composite primary key above till now. Let us now check with the help of desc command.
mysql> desc CompositePrimaryKey;
The following is the output.
+-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | Id | int(11) | YES | | NULL | | | StudentName | varchar(100) | YES | | NULL | | | Age | int(11) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 3 rows in set (0.09 sec)
Look at the above sample output, there is no primary keyword. This itself says that no composite primary key is available.
Now, let us use ALTER command to add composite primary key. The query is as follows.
mysql> ALTER table CompositePrimaryKey add primary key(Id,StudentName); Query OK, 0 rows affected (1.26 sec) Records: 0 Duplicates: 0 Warnings: 0
Above, I have added composite primary key with the column name “Id” and “StudentName”. To check the same, we can use DESC command. The query is as follows.
mysql> DESC CompositePrimaryKey;
Here is the output.
+-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | Id | int(11) | NO | PRI | NULL | | | StudentName | varchar(100) | NO | PRI | NULL | | | Age | int(11) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
As we can see in the above output, “PR” means we have successfully added composite primary key on column Id and StudentName.
875 Views
