What does a CONSTRAINT have to do with my unique index?

All we need is an easy explanation of the problem, so here it is.

I have to indexes on my table. The first was created by Django and the second by me. I’m not completely certain what the extra CONSTRAINT means in the first index and am wondering how I would change my CREATE INDEX statement to add that to it.

"customers_phonetype_customer_id_176731583d230ba5_uniq" UNIQUE CONSTRAINT, btree (customer_id, display) ;-- Django created
"customers_phonetype_customer_id_uniq" UNIQUE, btree (customer_id, lower(display::text)) ; -- Manually created

This is how I manually created the second index:

create unique index customers_phonetype_customer_id_uniq on customers_phonetype (customer_id, lower(display));

What is the meaning of the extra word CONSTRAINT in this case? I’m not seeing it in the docs.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The first one is a unique constraint. It can be added to an existing table with:

ALTER TABLE ADD CONSTRAINT ...

Details in the manual here.
It is implemented using a unique index. Per documentation:

Adding a unique constraint will automatically create a unique btree
index on the column or group of columns used in the constraint. A
uniqueness constraint on only some rows can be enforced by creating a partial index.

In addition it allows foreign key references to it.

The second one in a unique index.
It couldn’t be a unique constraint because those only allow columns, not expressions. More details:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply