Opened 10 years ago

Closed 5 years ago

#4192 closed Bug (invalid) sqlindexes does not create the index for unique_together fields

Reported by: litnimax Owned by: nobody
Component: Core (Management commands) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Pls see example below:


class SpeedDial(models.Model):

    account = models.ForeignKey(VoipAccount, editable=False)
    sequence = models.CharField(maxlength=1)
    number = models.CharField(maxlength=20)

    def __str__(self):
        return '%s' % self.sequence

    class Admin:
        list_display = ('sequence', 'number')
        list_display_links = ('sequence', 'number')

    class Meta:
        ordering = ('sequence', )
        unique_together = (('sequence','account'),)        
max@explorer ~/Development/public/dtl/r_manager/trunk/rmanager $ python  sqlall pbx
CREATE TABLE `pbx_speeddial` (
    `sequence` varchar(1) NOT NULL,
    `number` varchar(20) NOT NULL,
    UNIQUE (`sequence`, `account_id`)
CREATE INDEX `pbx_speeddial_account_id` ON `pbx_speeddial` (`account_id`);

But when we issue:

max@explorer ~/Development/public/dtl/r_manager/trunk/rmanager $ python  sqlindexes pbx
CREATE INDEX `pbx_speeddial_account_id` ON `pbx_speeddial` (`account_id`);

it only generates index for foreign key.

What about uniq index!?

Change History (7)

comment:1 Changed 9 years ago by Simon G. <dev@…>

Summary: sqlindexes generates wrong sql sqlindexes does not create the index for unique_together fields
Triage Stage: UnreviewedAccepted

Confirmed as of r5798

comment:2 Changed 9 years ago by Eratothene

It looks like the unique contraint also causes the index to disappear.

class A(models.Model):

    a = models.CharField(maxlength=1, unique=True, db_index=True)

instead no index, just unique constraint

comment:3 Changed 7 years ago by Renato Alves

If your backend is MySQL (I don't about other backends), UNIQUE and PRIMARY KEY columns are automatically (internally) indexed.

You don't need to explicitly add other indexes to the same columns.

Also from a construction point of view, UNIQUE is not a standard INDEX as it will fail if the column in question has non unique content. Therefore from my point of view, it should be part of the CREATE TABLE and not of CREATE INDEX statements.

In summary, the original decision is supported by the same reasons that made syncdb not to issue ALTER TABLE statements.

comment:4 Changed 6 years ago by Łukasz Rekucki

Severity: Normal
Type: Bug

comment:5 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:6 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:7 Changed 5 years ago by Anssi Kääriäinen

Resolution: invalid
Status: newclosed

Why should sqlindexes produce CREATE UNIQUE INDEX command? It is an implementation detail of the DB how it enforces the unique index. As far as I know, every supported database automatically creates an unique index to enforce the constraint, but there is no reason why Django should do that. In fact, it is probably wrong to produce both CREATE UNIQUE INDEX (a) and UNIQUE(a).

If there is some database where the CREATE UNIQUE INDEX is needed, then that is a clear bug, and needs its own ticket.

Note: See TracTickets for help on using tickets.
Back to Top