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

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.

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

Previous: 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.