MySQL Alter Table Statement Exercises: Add a primary key for the columns location_id in the locations table

MySQL Alter Table Statement: Exercise-8 with Solution

Write a MySQL statement to add a primary key for the columns location_id in the locations table.

Here is the sample table locations.

location_id  street_address        postal_code  city        state_province  country_id
-----------  --------------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola di Rie  989          Roma                        IT
1100         93091 Calle della Te  10934        Venice                      IT
1200         2017 Shinjuku-ku      1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-cho       6823         Hiroshima                   JP
1400         2014 Jabberwocky Rd   26192        Southlake   Texas           US
1500         2011 Interiors Blvd   99236        South San   California      US
1600         2007 Zagora St        50090        South Brun  New Jersey      US
1700         2004 Charade Rd       98199        Seattle     Washington      US
1800         147 Spadina Ave       M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood St       YSW 9T2      Whitehorse  Yukon           CA
...		   

View the table

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 primary key constraint.
-- The primary key constraint is added to the 'location_id' column.

ALTER TABLE locations

-- Add a primary key constraint to the 'location_id' column in the 'locations' table.
ADD PRIMARY KEY(location_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)   | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

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);: It adds a primary key constraint to the 'location_id' column in the 'locations' table. A primary key constraint ensures that the values in the specified column (in this case, 'location_id') are unique and not null. The primary key constraint is used to uniquely identify each row in the table.

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

Previous: Write a SQL statement to change the name of the column state_province to state, keeping the data type and size same.
Next: Write a SQL statement to add a primary key for a combination of columns location_id and country_id.