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: > On Mon, Aug 10, 2009 at 3:52 PM, physicsnick <physicsn...@gmail.com> 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 django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
