How To Create A Composite Primary Key In Mysql

Technical Questions and Answers

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.

Published on 30-Nov-2018 10:44:06

Related Questions & Answers
How to identify composite primary key in any MySQL database table?
MySQL ALTER column to remove primary key and auto_increment?
How to get primary key of a table in MySQL?
Can we remove a primary key from MySQL table?
How to reset the primary key of a table in mysql?
How can we remove composite PRIMARY KEY constraint applied on multiple columns of an existing MySQL table?
How to make MySQL table primary key auto increment?
How can I define a column of a MySQL table PRIMARY KEY without using the PRIMARY KEY keyword?
Remove Primary Key in MySQL?
Reset Primary Key in MySQL
How to refer primary key as Foreign to various table in MySQL?
Finding Duplicate Rows in MySQL (Composite Key)?
Composite Key in RDBMS
How can we alter table to add MySQL virtual GENERATED COLUMNS?
How can we alter table to add MySQL stored GENERATED COLUMNS?