On Mon, Aug 10, 2009 at 3:52 PM, physicsnick <[email protected]> wrote:
> > 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? > 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 --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
