0

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.

1 Answer 1

2

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:

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.