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.