How to Add a Unique Constraint After Creating a Table in PostgreSQL

Today, we’ll learn how to add a UNIQUE constraint to rows in a table after they have already been created.

The UNIQUE constraint guarantees that the data in a row is unique in that column. So if there is a column ID, all the rows will have unique values and not repetitive ones.

However, having null values in more than two rows can happen even with the constraint.

But what if we forgot to add UNIQUE to that column we wanted to? Today, we will look at how we can add a unique constraint to our desired column after creating a table.

Use the alter Statement to Add a Unique Constraint After Creating a Table in PostgreSQL

The alter statement supports the inclusion of UNIQUE. We’ll use an already made table called Dog in our example.

This table has a tag as the primary key and an owner_id. The owner_id isn’t set to UNIQUE because it will perfectly work if we insert the following.

insert into dog values (14, 2);

Output:

But now, let’s add the UNIQUE constraint to the column owner_id and see what happens when we call insert.

alter table dog add constraint uniq_test UNIQUE (owner_id);

Now, when we call the insert for the duplicate owner_id in the data; (14, 2), this is the error PostgreSQL will return.

ERROR:  duplicate key value violates unique constraint "uniq_test"
DETAIL:  Key (owner_id)=(2) already exists.

The UNIQUE constraint we defined as uniq_test is violated.

But what if we already had duplicate values in the table and added the UNIQUE constraint? In that case, the ALTER will fail with the error:

ERROR:  could not create unique index "uniq_test"
DETAIL:  Key (owner_id)=(2) is duplicated.

The constraint addition failed as there were duplicate keys already present. So how do we go around this problem?

If you already have a large table and you cannot remove the duplicate values, you cannot add the constraint, even if you use the older query such as this one:

create unique index uniq_test on dog (owner_id);

It will return a DUPLICATE error.

PostgreSQL does that because if you are adding UNIQUE to a column, it means all values have to be unique.

If you already have repeated values, you cannot expect the column to be unique; thus, this is depreciated.