id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
24082	Unique=True on TextField or CharField should not create an index	djbug	nobody	"
I've experienced this with PostgreSQL but I suspect it could be true for other databases too. 

[http://www.postgresql.org/docs/9.3/static/indexes-unique.html PostgreSQL docs] say: 

{{{
there's no need to manually create indexes on unique columns; doing so would just duplicate the automatically-created index.
}}}

Further the docs say:

`The index covers the columns that make up the [...] unique constraint [...] and is the mechanism that enforces the constraint.`

However this model in Django with `unique=True` on a `TextField` creates an index on top of the unique constraint.  
{{{

class Book(models.Model):
    name = models.TextField(unique=True)
}}}

creates following table & constraints in PostgreSQL:

{{{
CREATE TABLE book (
    id integer NOT NULL,
    name text NOT NULL,
);

ALTER TABLE ONLY book ADD CONSTRAINT book_name_key UNIQUE (name);
CREATE INDEX book_name_like ON book USING btree (name text_pattern_ops);
}}}

Please correct me if I'm wrong. My conclusion is that database enforce unique constraint by way of an index. Adding another index is a waste. There's some mention of this fact in an old bug report ([https://code.djangoproject.com/ticket/3030#comment:3 comment 3] & [https://code.djangoproject.com/ticket/3030#comment:6 comment 6] ) but it looks like the issue got dropped.

However, if the justification to add a second index is [https://code.djangoproject.com/ticket/12234 Bug Report 12234] then it might be more efficient to interpret a unique constraint as 


{{{
CREATE UNIQUE INDEX ""book_name_like_idx"" ON ""book"" (""name"" text_pattern_ops);

}}}

instead of a `UNIQUE` constraint, an `INDEX` and an implicit index by the database. 
"	Bug	new	Database layer (models, ORM)	1.7	Normal				Unreviewed	0	0	0	0	0	0