How to Drop Primary Key in PostgreSQL

Shihab Sikder Feb 02, 2024
How to Drop Primary Key in PostgreSQL

This article discusses using the ALTER TABLE query to drop the primary key constraint in PostgreSQL.

Drop Primary Key in PostgreSQL

You have a table, and you want to delete the primary key field. Watch the following table of the employee.

Using \d table_name, we can see each column’s field name and type.

postgres=# \d record
                       Table "public.record"
  Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
 id       | integer               |           | not null |
 username | character varying(50) |           |          |
 email    | character varying(50) |           |          |
 gender   | character varying(50) |           |          |
 company  | character varying(50) |           |          |
Indexes:
    "record_pkey" PRIMARY KEY, btree (id)

The code snippet shows that id is the primary key, and the constraint is record_pkey. Now let’s drop the primary key column.

The syntax of deleting an existing primary key.

ALTER TABLE table_name DROP CONSTRAINT primary_key_constraint;

Here we have a table named record. To delete the id from the record table, the query will be:

ALTER TABLE record
DROP CONSTRAINT record_pkey;
postgres=# ALTER TABLE record
postgres-# DROP CONSTRAINT record_pkey;
ALTER TABLE

Now, let’s see the table description. We shouldn’t see the line.

"record_pkey" PRIMARY KEY, btree (id)

The ID column will still be in the table because we’re removing the primary key constraint, not the entire column. It will not be treated as a primary key.

Type \d records, and now you should get the following output.

postgres=# \d record;
                       Table "public.record"
  Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
 id       | integer               |           | not null |
 username | character varying(50) |           |          |
 email    | character varying(50) |           |          |
 gender   | character varying(50) |           |          |
 company  | character varying(50) |           |          |


postgres=#

There are many ALTER TABLE queries; you can look at the official documentation. Also, you can edit the primary key column, meaning you can change the primary key.

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website

Related Article - PostgreSQL Key