How to add a constraint on multiple columns to prevent duplicate rows?

Question

I have an existing table in a Postgres database that accumulated duplicate values. We've cleared the table and repopulated the values, but want to prevent duplicates from occurring again. Thus we need a to add a constraint.

Table name: camembert, sample data:

| handle | id  | num   |
| ------ | --- | ----- |
| 259    | 869 | AC003 |
| 259    | 869 | AC003 |
| 259    | 869 | AC003 |
| 259    | 856 | AC005 | 
| 259    | 856 | AC005 |
| 259    | 856 | AC005 |
etc.

Below is an example of the issue (we actually have triplicates of everything)

The constraint should allow the first two columns to have duplicate values, however, the third column is the differentiator that makes the row unique. For example, when the table is properly populated it looks as follows:

| handle | id    | num   |
| ------ | ----- | ----- |
| 259    | 869   | AC003 |
| 259    | 856   | AC005 |
| 259    | 856   | AC007 |
| 47138  | 41085 | AC003 |
| 47138  | 41085 | AC005 |
| 47138  | 43513 | AC007 |

I attempted the following script:

ALTER TABLE camembert ADD CONSTRAINT num_uniq UNIQUE (num);

But got an error:

ERROR: could not create unique index "num"
Detail: Key (num)=(AC003) is duplicated.

What am I misunderstanding about the unique constraint? What's the key statement to unlock this puzzle?

@a_horse_with_no_name, no the uniqueness is over three columns(handle, id, num) so there can be duplicate num values. The solution is the answer from JGH . — Adrian Klaver
– Adrian Klaver, Commented Mar 18, 2021 at 19:56

JGH · Accepted Answer · 2021-03-18 19:53:14Z

7

you must add the constraint on the 3 columns together

ALTER TABLE camembert ADD CONSTRAINT num_vals UNIQUE (handle, id, num);

but before doing so, you must have removed the duplicated.

answered Mar 18, 2021 at 19:53

JGH

18.2k5 gold badges40 silver badges57 bronze badges

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

Comments

Erwin Brandstetter · Accepted Answer · 2021-03-18 20:09:00Z

You need a multicolumn UNIQUE or PRIMARY KEY constraint.

CREATE TABLE camembert (
  handle int
, id int
, num text
, PRIMARY KEY (handle, id, num)
);

In the case of a UNIQUE constraint, you probably also want all three columns to be marked NOT NULL.

To alter an existing table use the ALTER TABLE syntax you already tried.

To enforce your requirement, the order of columns in the constraint is insignificant. It can matter for performance, though. As either constraint is implemented with a unique index internally, put the columns you query the most in front. And you may want to create additional indexes.

Collectives™ on Stack Overflow

How to add a constraint on multiple columns to prevent duplicate rows?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related