PostgreSQL Drop Primary Key

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.

Write for us
DelftStack articles are written by software geeks like you. If you also would like to contribute to DelftStack by writing paid articles, you can check the write for us page.

Related Article - PostgreSQL Key

  • Foreign Key Syntax in PostgreSQL