Opened 14 years ago

Closed 14 years ago

Last modified 14 years ago

#12421 closed (worksforme)

Foreign Key on Non-Primary Field fails due to lack of Index on Related Field w/ MySQL

Reported by: anonymous Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Keywords:
Cc: clouserw@… Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

SVN Revision 11924

class Relation(models.Model):
    code = models.CharField(max_length=2, db_index=True)
    class Meta:
        db_table = u'relation_codes'
class Source(models.Model):
    code = models.ForeignKey('Relation', to_field='code')
    class Meta:
        db_table = u'source_codes'

When trying to create the database, I get a "_mysql_exceptions.OperationalError (#150)" on a query with the form "ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY ... REFERENCES" for my foreign key relationship, checking the obvious problems I find that my table to which I'm trying to make a relationship with has no index on the field I wish to make a foreign key relationship with. Adding an index allows for the problem query to be executed successfully. I am assuming an order problem with the indices for non-primary (or non-integer) fields being created after foreign keys are added leading to the problem.

Change History (5)

comment:1 by Russell Keith-Magee, 14 years ago

Triage Stage: UnreviewedAccepted

comment:2 by Ramiro Morales, 14 years ago

Resolution: worksforme
Status: newclosed

I tset this (using a t12421 app) and MySQL 5.0 by creating only the Relation model, the sqlall output shows the index is correctly being created:

r@host:~/src/dtest08$ PYTHONPATH=~/django/trunk ./manage.py sqlall t12421
BEGIN;
CREATE TABLE `relation_codes` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `code` varchar(2) NOT NULL
)
;
CREATE INDEX `relation_codes_code` ON `relation_codes` (`code`);
COMMIT;

The index is being created.

I suspect you might have added the db_index=True to the code field of Relation after the relation_codes table was created. Remember syncdb works with a model-level granularity to create tables; if you need you will need to devise or use a third-party schema migration tool.

Another probable (although a weird one )reason for the index not being present is if MySQL isn't creating it even if is is being told to do so. But we 'd need more details about the setup.

Closing this as worksforme. Please reopen if you can provide more details and a a detailed sequence to reproduce this, or if I missed something.

comment:3 by Wil Clouser, 14 years ago

Cc: clouserw@… added
Resolution: worksforme
Status: closedreopened

I can reproduce this with the following model:

from django.db import models

class Item(models.Model):
    license = models.ForeignKey('License', to_field="name", null=True)

    class Meta:
        db_table = 'testo_item'

class License(models.Model):
    name = models.PositiveIntegerField(db_index=True)

    class Meta:
        db_table = 'testo_license'

An important point to note is that I'm forcing InnoDB in my settings.py (which is the difference between this failing and not):

DATABASES = { 
    'default': { 
        ...
        'OPTIONS': {'init_command': 'SET storage_engine=InnoDB'},
    },
}

At that point running sqlall will show you the order the commands are executed:

$ ./manage.py sqlall testo
BEGIN;
CREATE TABLE `testo_item` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `license_id` integer UNSIGNED
)
;
CREATE TABLE `testo_license` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` integer UNSIGNED
)
;
ALTER TABLE `testo_item` ADD CONSTRAINT `license_id_refs_name_a4fa988` FOREIGN KEY (`license_id`) REFERENCES `testo_license` (`name`);
CREATE INDEX `testo_item_license_id` ON `testo_item` (`license_id`);
CREATE INDEX `testo_license_name` ON `testo_license` (`name`);
COMMIT;

Note that it's trying to add the foreign key before it creates the indexes. Copying and pasting the above output will work fine, unless your default table type is InnoDB (or you can modify the CREATE TABLE statements and add ENGINE=InnoDB).

comment:4 by Sasha Romijn, 14 years ago

Resolution: worksforme
Status: reopenedclosed

I can not reproduce this with your model with Django trunk, because syncdb/sqlall refuse db_index=True unless I also set unique=True on License.name.

Error: One or more models did not validate:
foo.item: Field 'name' under model 'License' must have a unique=True constraint.

Having written the model like this:

from django.db import models

class Item(models.Model):
    license = models.ForeignKey('License', to_field="name", null=True)

    class Meta:
        db_table = 'testo_item'

class License(models.Model):
    name = models.PositiveIntegerField(db_index=True)

    class Meta:
        db_table = 'testo_license'

The SQL becomes this, which is valid for InnoDB:

BEGIN;
CREATE TABLE `testo_item` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `license_id` integer UNSIGNED
)
;
CREATE TABLE `testo_license` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` integer UNSIGNED NOT NULL UNIQUE
)
;
ALTER TABLE `testo_item` ADD CONSTRAINT `license_id_refs_name_a4fa988` FOREIGN KEY (`license_id`) REFERENCES `testo_license` (`name`);
CREATE INDEX `testo_item_139668fe` ON `testo_item` (`license_id`);
COMMIT;

The code will simply not allow me to set db_index unless I set unique.

Closing this again as worksforme.

comment:5 by Wil Clouser, 14 years ago

Indeed, it looks like this won't occur after #11702. I agree this is fixed.

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