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

[anonymous]

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.

[Ramiro Morales]

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.

[Wil Clouser]

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).

[Sasha Romijn]

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.

[Wil Clouser]

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