add uniqueness constraint when there are already duplicate old records

Question

Say I have a table :employers and :employee. And I wanted to add the following:

ALTER TABLE employers ADD UNIQUE (employee_id);

This worked fine in development but since there are already duplicate records in production, it is not working in production.

Is there a way to add sequence number of uniqueness constraint, i.e. impose uniqueness constraint on only new records?

You need to delete the duplicates before you can create the constraint — user330315
– user330315, Commented Oct 20, 2016 at 16:49
The semantics of the question are vague. What should happen if a new record conficts with one or more old records? — wildplasser
– wildplasser, Commented Oct 20, 2016 at 18:33
if a new record conflicts with an old record, an uniqueness error will arise. I just don't want to mess up existing records that may have duplicates. — LOCKLOCK
– LOCKLOCK, Commented Oct 20, 2016 at 22:19

Clodoaldo Neto · Accepted Answer · 2016-10-20 17:18:21Z

4

Create a partial unique index

create unique index index_unique_employer_id on employers(employer_id) where employer_id > 10

https://www.postgresql.org/docs/current/static/sql-createindex.html

answered Oct 20, 2016 at 17:18

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HLGEM Over a year ago

This is usually a bad idea. It is far better to fix the duplicated data. You might find that there is code inserting the duplicates which could cause a problem on production. Especially if multiple different applications/imports insert data and each is designed differently. You might find that something does an insert rather than an update. Understanding what caused the duplicates amn knowing that you have fixed the cause is critical because if this is a unique field, then reports and exports are going to get into problems as well if you don't fix the bad data you currently have.

LOCKLOCK Over a year ago

thanks! it is not feasible to fix duplicated data as they are valid. It is more like a change in business logic than bad data. In this way, will where employer_id > 10 be a good solution?

Collectives™ on Stack Overflow

add uniqueness constraint when there are already duplicate old records

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related