syncdb with ForeignKey to_field outputs statements in wrong order

[physicsnick]

Hello,

I'm trying to use ForeignKey on a specific indexed column using the
to_field attribute. Unfortunately syncdb seems to be outputting the
ADD CONSTRAINT statement before the CREATE INDEX on the to_field, so
mysql refuses to add the constraint and syncdb quits with an
exception.

Here's a simple example:


from django.db import models

class Author(models.Model):
code = models.CharField(max_length=10, db_index=True)
first_name = models.CharField(max_length=30)
last_name = models.CharField(max_length=40)

class Book(models.Model):
title = models.CharField(max_length=100)
author = models.ForeignKey(Author, to_field='code')


Here, the Author class has an indexed 'code' column, a short
alphanumeric string that identifies it; we want the Book's foreign key
to use this rather than the numeric id.

This is the output of "./manage.py sqlall books" using MySQL with
InnoDB default tables:


BEGIN;
CREATE TABLE `books_author` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`code` varchar(10) NOT NULL,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(40) NOT NULL
)
;
CREATE TABLE `books_book` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`title` varchar(100) NOT NULL,
`author_id` varchar(10) NOT NULL
)
;
ALTER TABLE `books_book` ADD CONSTRAINT `author_id_refs_code_36b0cc23`
FOREIGN KEY (`author_id`) REFERENCES `books_author` (`code`);
CREATE INDEX `books_author_code` ON `books_author` (`code`);
CREATE INDEX `books_book_author_id` ON `books_book` (`author_id`);
COMMIT;


This is not correct. The index on books_author(code) needs to be
created before the foreign key constraint is added, otherwise it will
fail because the target columns of foreign keys need to be indexed.
When I run syncdb, it fails with the following error (which you can
see if you just paste the above sql into a temporary database):

_mysql_exceptions.OperationalError: (1005, "Can't create table
'testfk.#sql-12a3_81' (errno: 150)")


In my case I need to run syncdb and wait for it to fail, then manually
create the index I need, then run syncdb again (and repeat, since I
have many foreign keys with custom columns). Or I need to not use
syncdb at all, and instead use the sqlall command and reorder the
statements myself.

Is this a django bug? Am I doing something wrong?

[Karen Tracey]

Why are you specifying db_index=True instead of unique=True on these fields that are targets of foreign keys?  They need to be unique if the many-to-one nature of ForeignKey is to be maintained.  If you use unique=True I do not think you will encounter the problem you are seeing.  (I think it is a bug that Django-level validation does not require that the to_field value be a field with unique=True.)

Karen

[physicsnick]

Actually they don't need to be unique (and I don't want them to be
unique). MySQL does not require that foreign keys be unique; only that
they be indexed.

I am actually using this for a temporal database. There are many
instances of an Author with the same code (an instance for each time
it was changed). In relation to the above example I am having the
Author's default manager filter for live instances of the object, and
making it be used to resolve foreign keys via "use_for_related_fields
= True". This means the reverse relationship (book.author) gives one
object: the live version of the author with the referenced code. While
the foreign key itself is a many-to-many relation at the database
level, it is meant to be filtered to a specific revision of the
database to resolve its parent (hence many-to-one).

This already all works. The only problem is that syncdb is not
creating the tables properly; we have to manually create them all by
re-ordering the sql.

Nick


On Aug 10, 8:49 pm, Karen Tracey <kmtra...@gmail.com> wrote:

[Malcolm Tredinnick]

On Tue, 2009-08-11 at 12:00 -0700, physicsnick wrote:
> Actually they don't need to be unique (and I don't want them to be
> unique). MySQL does not require that foreign keys be unique; only that
> they be indexed.
>
> I am actually using this for a temporal database. There are many
> instances of an Author with the same code (an instance for each time
> it was changed). In relation to the above example I am having the
> Author's default manager filter for live instances of the object, and
> making it be used to resolve foreign keys via "use_for_related_fields
> = True". This means the reverse relationship (book.author) gives one
> object: the live version of the author with the referenced code. While
> the foreign key itself is a many-to-many relation at the database
> level,

Django's ForeignKey field is many-to-one. The fact that it happens not
to raise an error when you twist it slightly in the way you're doing
doesn't mean it's expected to work.

I'm not convinced there is a bug involved here, since you're not using
the ForeignKey class correctly. As far as I can work out, when
ForeignKey is used correctly, we always are going to be generating the
correct SQL.

I thought Django's "validate" management command raised an error when
to_field was used to target a non-unique field, but apparently I'm
hallucinating. That's probably an oversight in the implementation.

Regards,
Malcolm

[physicsnick]

Hmm. It seems to be database-specific. PostgreSQL and Oracle require
that foreign key references be unique, while MySQL and SQLite do not
(although for SQLite that's mainly due to it not actually implementing
foreign key constraints). The MySQL documentation has this to say:

>> Additionally, MySQL and InnoDB require that the referenced columns be indexed for performance. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE.

... which we never do, because it's a temporal database. It does
recommend that they be unique anyway though.

I'll file a bug with Django to make the validate command require that
foreign keys reference unique columns, and to update the documentation
to actually say that the to_field should be unique. In the meantime
I'll have to make a reference field that supports foreign key
semantics (e.g. book.author and Author.book_set) that doesn't actually
subclass ForeignKey. :/

Thanks,

Nick


On Aug 11, 10:00 pm, Malcolm Tredinnick <malc...@pointy-stick.com>
wrote: