w3resource

PostgreSQL Alter Table: Alter a table to drop the existing primary key


8. Write a SQL 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.

postgres=# \d locations

     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
 location_id    | numeric(4,0)          | not null
 street_address | character varying(40) |
 postal_code    | character varying(12) |
 city           | character varying(30) |
 state_province | character varying(25) |
 country_id     | character varying(2)  | not null
Indexes:
    "locations_pkey" PRIMARY KEY, btree (location_id, country_id)

Now execute the following statement.

Sample Solution:

Code:

ALTER TABLE locations 
DROP CONSTRAINT locations_pkey;

Output:

Now see the structure of the table locations after alteration.

postgres=# \d locations

     Column     |         Type          | Modifiers
----------------+-----------------------+-----------
 location_id    | numeric(4,0)          | not null
 street_address | character varying(40) |
 postal_code    | character varying(12) |
 city           | character varying(30) |
 state_province | character varying(25) |
 country_id     | character varying(2)  | not null

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.

What is the difficulty level of this exercise?