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, Mar 18 '21 at 19:56

score 7 · Answer 1 · answered Mar 18 '21 at 19:53

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 '21 at 19:53

JGH

15,928
4
31
48

Erwin Brandstetter · Answer 2 · 2021-03-18T20:09:00.570

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.

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

2 Answers2