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.
