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?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
