MySQL Alter Table Statement Exercises: Drop the existing primary from the table locations on a combination of columns location_id and country_id

MySQL Alter Table Statement: Exercise-10 with Solution

Write a MySQL statement to drop the existing primary from the table locations on 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) | NO   | PRI | 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)   | NO   | PRI | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

Code:

-- This SQL statement is used to alter the 'locations' table by dropping the primary key constraint.
-- The existing primary key constraint for the table is being removed.

ALTER TABLE locations

-- Drop the primary key constraint from the 'locations' table.
DROP PRIMARY KEY;

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   |     | 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)   | NO   |     | 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.
• DROP PRIMARY KEY;: This part removes (drops) the primary key constraint from the 'locations' table. This means that the table will no longer have a primary key, and the constraint enforcing uniqueness and non-null values on a specific column or combination of columns will be removed.

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 a combination of columns location_id and country_id.
Next: Write a SQL statement to add a foreign key on job_id column of job_history table referencing to the primary key job_id of jobs table.