MySQL Alter Table Statement Exercises: Add a primary key for a combination of columns location_id and country_id

Last update on October 15 2025 12:48:40 (UTC/GMT +8 hours)

MySQL Alter Table Statement: Exercise-9 with Solution

Write a MySQL statement to add a primary key for a combination of columns location_id and country_id.

Here is the structure of the table locations.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID    | decimal(4,0) | YES  |     | NULL    |       |
| STREET_ADDRESS | varchar(40)  | YES  |     | NULL    |       |
| POSTAL_CODE    | varchar(12)  | YES  |     | NULL    |       |
| CITY           | varchar(30)  | YES  |     | NULL    |       |
| STATE_PROVINCE | varchar(25)  | YES  |     | NULL    |       |
| COUNTRY_ID     | varchar(2)   | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

Code:

 -- This SQL statement is used to alter the 'locations' table by adding a composite primary key constraint.
-- The composite primary key constraint is added to the 'location_id' and 'country_id' columns.

ALTER TABLE locations

-- Add a composite primary key constraint to the 'location_id' and 'country_id' columns in the 'locations' table.
ADD PRIMARY KEY(location_id, country_id);

Let execute the above code in MySQL command prompt

Now see the structure of the table locations after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID    | decimal(4,0) | NO   | PRI | 0       |       |
| STREET_ADDRESS | varchar(40)  | YES  |     | NULL    |       |
| POSTAL_CODE    | varchar(12)  | YES  |     | NULL    |       |
| CITY           | varchar(30)  | YES  |     | NULL    |       |
| STATE_PROVINCE | varchar(25)  | YES  |     | NULL    |       |
| COUNTRY_ID     | varchar(2)   | NO   | PRI |         |       |
+----------------+--------------+------+-----+---------+-------+

Here is the index file which have been created with creation of primary key.

+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| locations |          0 | PRIMARY  |            1 | LOCATION_ID | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| locations |          0 | PRIMARY  |            2 | COUNTRY_ID  | A         |           0 |     NULL | NULL   |      | BTREE      |         |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Explanation:

Here's a brief explanation of the above MySQL code:

ALTER TABLE locations: This part of the statement indicates that you want to make changes to the structure of the 'locations' table.
ADD PRIMARY KEY(location_id, country_id);: It adds a composite primary key constraint to the 'location_id' and 'country_id' columns in the 'locations' table. A composite primary key constraint ensures that the combination of values in these two columns is unique across all rows in the table.

Go to:

PREV : Write a SQL statement to add a primary key for the columns location_id in the locations table.
NEXT : Write a SQL statement to drop the existing primary from the table locations on a combination of columns location_id and country_id.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.