UNIQUE Constraint not working? (Postgresql)-CodePudding

I altered my postgresql database table to have a unique constraint like this:

ALTER TABLE my_table
ADD CONSTRAINT unique_values UNIQUE (value_one, value_two, value_three);

I want to not be able to post duplicate values for value_one, value_two, or value_three. So there should only ever be ONE unique value in these columns in the entire database.

But I'm still able to INSERT duplicate values in the value_one, value_two, and value_three columns.

What am I doing wrong?

Edit:

I have deleted all the duplicate data in the database, but get an error whenever I try to add a unique constraint to individual columns in the table.

Another thing, if I say:

ALTER TABLE my_table
ADD CONSTRAINT unique_values UNIQUE (value_one, value_two, value_three);

I do not get an error. But if I say:

ALTER TABLE my_table
ADD CONSTRAINT unique_values UNIQUE (value_one);

Like with just one value, I get this error:

could not create unique index "unique_values"

CodePudding user response：

If you define a unique constraint over three columns, that means that you cannot insert the same three values a second time. But each of these columns can contain duplicate values. For example, these three rows would satisfy the uniqueness constraint:

(1, 2, 3)
(1, 2, 4)
(1, 5, 3)

but you would get an error if you inserted one of these triples a second time.

If you want each column to be unique, you have to define three constraints, one for each column.