0

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"
Improve this question
8
  • Please post sample data, both rows that should and rows that should not insert. Post data as formatted text or even better a fiddle - no images. Commented Aug 25, 2022 at 20:01
  • 4
    unique constraint means that the combination of the three are unique and not individually Commented Aug 25, 2022 at 20:02
  • Thank you so much! That is the problem. When I do it individually, it says "could not create unique index..." I think because there are already duplicate values in the database. How would I add the contraint if there are already duplicates? Commented Aug 25, 2022 at 20:03
  • you search foe them as for all other duplicates and remove or change them as for your uniqueness, may be you should make a check constraint that checks for value in multiple columns Commented Aug 25, 2022 at 20:12
  • 1
    What is the complete error message? Add as update to your question. Commented Aug 25, 2022 at 22:16

1 Answer 1

Reset to default
1

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.

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

3 Comments

I know this is the right answer. But whenever I delete the duplicate values in the database, then try to add one constraint for each column, I still get the error: could not create unique index "unique_values" do you know why this is happening and how to fix it?
That is because there already conflicting data in the table. You will have to delete those data.
Thank you! I got it working finally. I did have duplicate values without realizing it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.