Code

Opened 7 years ago

Closed 2 years ago

#4192 closed Bug (invalid)

manage.py 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

Description

Pls see example below:

Model:

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 manage.py  sqlall pbx
BEGIN;
CREATE TABLE `pbx_speeddial` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `account_id` integer NOT NULL REFERENCES `ENTVOIPACCT` (`ATRACCTID`),
    `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`);
COMMIT;

But when we issue:

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

it only generates index for foreign key.

What about uniq index!?

Attachments (0)

Change History (7)

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

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Summary changed from manage.py sqlindexes generates wrong sql code to manage.py sqlindexes does not create the index for unique_together fields
  • Triage Stage changed from Unreviewed to Accepted

Confirmed as of r5798

comment:2 Changed 7 years ago by Eratothene

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

Example:
class A(models.Model):

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

Should be: CREATE UNIQUE INDEX
instead no index, just unique constraint

comment:3 Changed 5 years ago by rjalves

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 3 years ago by lrekucki

  • Severity set to Normal
  • Type set to Bug

comment:5 Changed 2 years ago by aaugustin

  • UI/UX unset

Change UI/UX from NULL to False.

comment:6 Changed 2 years ago by aaugustin

  • Easy pickings unset

Change Easy pickings from NULL to False.

comment:7 Changed 2 years ago by akaariai

  • Resolution set to invalid
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.