Add unique constraint after creating table in PostgreSQL

Current Location：Home > Learning > DATABASE > PostgreSQL >

Add unique constraint after creating table in PostgreSQL

Author：JIYIK Last Updated：2025/04/27 Views：

Today we will learn how to add constraints after the rows in a table have been created UNIQUE.

The UNIQUE constraint guarantees that the data in a row is unique in that column. So if the column ID exists, all rows will have unique values, rather than duplicate values.

However, even with the constraint, it is possible for more than two rows to have null values.

But what if we forget to add in the column we want UNIQUE? Today, we will look at how to add a unique constraint to the required column after creating the table.

After creating a table in PostgreSQL, use `alter`the statement to add a unique constraint

alterThe statement supports inclusion UNIQUE. In our example, we will use an Dogalready created table called .

Prepared table - dog

This table has a tagas primary key and a owner_id. owner_idis not set to UNIQUE, because if we insert the following it will work perfectly.

insert into dog values (14, 2);

Output:

dog table update

But for now, let's UNIQUEadd the constraint to the column and see what happens owner_idwhen we call .insert

alter table dog add constraint uniq_test UNIQUE (owner_id);

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

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

The constraint we defined as uniq_testis UNIQUEviolated.

UNIQUEBut what if we already have duplicate values in the table and add the constraint? In this case, ALTERit will fail with the following error:

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

The constraint addition fails because a duplicate key already exists. So how do we fix this?

If you already have a large table and cannot remove duplicate values, you cannot add constraints, even if you use an older query such as the following:

create unique index uniq_test on dog (owner_id);

It will return an DUPLICATEerror.

PostgreSQL does this because if you UNIQUEadd to a column, it means all values must be unique.

If you already have duplicate values, you can't expect the column to be unique; therefore, this is discarded.

Previous：Difference between timestamps with and without time zone in PostgreSQL

Next：Selecting whether a string contains a substring match in PostgreSQL

For reprinting, please send an email to 1244347461@qq.com for approval. After obtaining the author's consent, kindly include the source as a link.

Article URL：

JIYIK CN >