unique=True and db_index=True leads to duplicated indexes

[fte@…]

At least in MySQL this produces a doubled index.

Example:

class Kunde(models.Model):
    kundenid  = models.AutoField(primary_key=True)
    loginname = models.CharField(unique=True, maxlength=50, db_index=True)

produces:

CREATE TABLE `mgt_kunden` (
    `kundenid`integer AUTO_INCREMENT NOT NULL PRIMARY_KEY,
    `loginname` varchar(50) NOT NULL UNIQUE
);
CREATE UNIQUE INDEX mgt_kunden_loginname ON `mgt_kunden` (`loginname`);

So there is the automatically generated index from the UNIQUE keyword
and the additional index requested by db_index=True.

[anonymous]

ghaloo
class Kunde(models.Model):

kundenid = models.AutoFi

is it at [url=​http://shurl.org/SGqxN]abba[/url] mkay?!?!
Models: Use of unique=True AND db_index=True at the sa
Models: Use of unique=True AND db_index=True at the same field should be flagged as an error
Status: new
Reported by: fte@… Assigned to: adrian
Priority: low Milestone: Version 1.0
Component: django-admin.py Version: SVN
Severity: trivial Keywords: manage.py sqlall
Cc:

At least in MySQL this produces a doubled index.

Example:

class Kunde(models.Model):

kundenid = models

[(none)]

Milestone Version 1.0 deleted

[torne-django@…]

This definately generates incorrect SQL under MySQL. The UNIQUE constraint on the field itself produces an index named after the field, and the index created is named tablename_fieldname, so the result is two indexes. Using them both shouldn't be an error, I don't think - instead, the CREATE INDEX can simply be omitted (as an index is implicitly created by the UNIQUE constraint). No idea about other DB backends though.

[Simon G. <dev@…>]

Both MySQL and SQLite doubles the index (can someone confirm on Postgres?), but doesn't complain, so you end up with these indexes:

 PRIMARY  	 PRIMARY  	 0   	 Edit   	 Drop   	 kundenid
loginname 	UNIQUE 	0  	Edit 	Drop 	loginname
t3030_kunde_loginname 	UNIQUE 	0  	Edit 	Drop 	loginname

This is fairly trivial, since it works, but does increase the key size (and therefore database size etc) and is not terribly elegant.

[Simon G. <dev@…>]

Actually - should db_index be creating a UNIQUE index at all? shouldn't it just be creating an INDEX (which is quite different)

[Malcolm Tredinnick]

Simon G: the db_index attribute checks to see if the index should be unique or not (so it examines the "unique" attribute as well). See django/core/management.py at the top of the get_sql_indexes_for_model() function for the details.

I think the small bug here is that the original code didn't take into account the fact that a "unique" constraint will construct an index in (almost) all databases because it is an efficient way of checking for uniqueness. So we may be able to short-circuit the index creation in those cases.

A fix for this ticket needs to check the behaviour of MySQL, PostgreSQL, SQLite (and preferably get some feedback on Oracle's behaviour as well) and confirm that they all create an index for "unique" fields. From memory, the answer is "yes", but I haven't explicitly checked it.

[Matt Boersma]

Oracle behaves the same way, emitting a (benign) error when it tries to create the redundant index.

[Gábor Farkas <gabor@…>]

this is a problem with PostgreSQL too.

also, this is probably obvious, but nevertheless..

not just exlicit "db_index=True" fields are a problem (where as a 'fix' you can simply omit the db_index=True)

having a SlugField(unique=True) has the same problem, and in that case, there's no way to avoid it (except deleting the index manually in the database

[Erin Kelly]

I'm closing this because I believe it was fixed by the addition of the autoindexes_primary_keys backend feature back when the Oracle branch was merged in. Please re-open if that's not the case.

[Peter Melvyn]

I've test in r7411 and the problem persists. if unique=True and db_index=True, there is still redundant CREATE UNIQUE INDEX .... SQL command, because of MySQL creates on UNIQUE column declaration inherent one.

[Peter Melvyn <peter.melvyn@…>]

I verified on MySQL 5.0.37 using InnoDB engine that setting DatabaseFeature.autoindexes_primary_keys to True really solves this problem. But in the backend.mysql.base, this feature is set to False. I didn't care about MySQL before version 5.x so I don't know what features have its older versions.

[Peter Melvyn <peter.melvyn@…>]

Because I don't know what was the behaveour of MySQL before version 5, I set DatabaseFeature?.autoindexes_primary_keys to True from MySQL server version 5++.

[Ramiro Morales]

See also #5680 that takes the approach of simply doing away with the autoindexes_primary_key DatabaseFeatures property.

[Peter Melvyn <peter.melvyn@…>]

Sure: In #5680 "Oracle guys" have introduced the new DB Feature autoindexes_primary_key and used it in logic generating indeces. But they left its value for MySQL equal to False, hence it has no effect. My patch sets its value to True, if version of MySQL server (read in run-time) is >= 5. This is from backward compatibility reason, because I've ignored MySQL before version 5 per se and I have no expirience, what happen if this feature would be set True while running obsolete MySQL version.

[Malcolm Tredinnick]

I'm going to close this in favour of the approach in #5680. Less options are better and all backends behave the same here (even MySQL 4.x).