How to Add Primary Key to a PostgreSQL Table Only if It Does Not Exist
- Assign a Primary Key in an Existing Table in PostgreSQL
- Add Primary Key to a PostgreSQL Table if It Does Not Exist
Sometimes, we may create a table without declaring the primary key, which is necessary for creating a relation with other tables. Now, we can update the table with the alter query and update the primary key.
Assign a Primary Key in an Existing Table in PostgreSQL
The primary key is necessary when creating a relationship. There can be multiple relations with a table, but without a primary key, we can’t make a relation.
Let’s say we’ve created a table like the following:
CREATE TABLE Product(
PRODUCT_ID INT,
PRODUCT_NAME VARCHAR,
PRODUCT_PRICE NUMERIC,
PRODUCT_DESCRIPTION VARCHAR
);
Now, if we want to see the table configuration and data types, we can use \d Product to get the following:
Table "public.product"
Column | Type | Collation | Nullable | Default
---------------------+-------------------+-----------+----------+---------
product_id | integer | | |
product_name | character varying | | |
product_price | numeric | | |
product_description | character varying | | |
Now, let’s add the primary key. Here, we will assign the product_id as the primary key.
Here’s how we will do it:
ALTER TABLE Product ADD PRIMARY KEY (PRODUCT_ID);
postgres=# ALTER TABLE Product ADD PRIMARY KEY (PRODUCT_ID);
ALTER TABLE
postgres=# \d Product\
Table "public.product"
Column | Type | Collation | Nullable | Default
---------------------+-------------------+-----------+----------+---------
product_id | integer | | not null |
product_name | character varying | | |
product_price | numeric | | |
product_description | character varying | | |
Indexes:
"product_pkey" PRIMARY KEY, btree (product_id)
postgres=#
Here, you can see that product_id is assigned as the primary key and can’t be null.
Add Primary Key to a PostgreSQL Table if It Does Not Exist
The above method adds the primary key even if there’s the primary key. Now, we will check and add the primary key if it doesn’t exist.
Here’s a thing to notice. Postgres will automatically assign a constraint like product_pkey whenever we add a primary key, so we need to check if this constraint exists or not.
Here are the steps:
- Drop the primary key if it exists.
- Add the primary key.
ALTER TABLE product DROP CONSTRAINT IF EXISTS product_pkey;
ALTER TABLE product ADD CONSTRAINT product_pkey PRIMARY KEY (PRODUCT_ID);
Here’s the output of the psql shell:
postgres=# ALTER TABLE product DROP CONSTRAINT IF EXISTS product_pkey;
NOTICE: constraint "product_pkey" of relation "product" does not exist, skipping
ALTER TABLE
postgres=# ALTER TABLE product ADD CONSTRAINT product_pkey PRIMARY KEY (PRODUCT_ID);
ALTER TABLE
postgres=# \d Product
Table "public.product"
Column | Type | Collation | Nullable | Default
---------------------+-------------------+-----------+----------+---------
product_id | integer | | not null |
product_name | character varying | | |
product_price | numeric | | |
product_description | character varying | | |
Indexes:
"product_pkey" PRIMARY KEY, btree (product_id)
To know more about the primary key and how to alter a table in Postgres, visit the following blog.
