id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
8316	MySQL error with custom primary_key that is not an IntegerField	Julian Bez	Julian Bez	"Create a model with a custom primary key field that is not an IntegerField and another with a foreign key pointing to it:

{{{
class Author(models.Model):
    id = models.PositiveIntegerField(primary_key=True)
    name = models.CharField(max_length=200)

class Article(models.Model):
    title = models.CharField(max_length=200)
    author = models.ForeignKey(Author)
}}}

Let's see how the SQL looks:

{{{
BEGIN;
CREATE TABLE `dbtest_author` (
    `id` integer UNSIGNED NOT NULL PRIMARY KEY,
    `name` varchar(200) NOT NULL
)
;
CREATE TABLE `dbtest_article` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `title` varchar(200) NOT NULL,
    `author_id` integer NOT NULL
)
;
ALTER TABLE `dbtest_article` ADD CONSTRAINT author_id_refs_id_6bbed69f FOREIGN KEY (`author_id`) REFERENCES `dbtest_author` (`id`);
CREATE INDEX `dbtest_article_author_id` ON `dbtest_article` (`author_id`);
COMMIT;
}}}

You can see that author_id should reference dbtest_author.id, but they have different data types.

Calling syncdb:

{{{
Creating table dbtest_author
Creating table dbtest_article
Traceback (most recent call last):
  File ""...manage.py"", line 11, in <module>
    execute_manager(settings)
  File ""C:\Programme\django_src\django\core\management\__init__.py"", line 334, in execute_manager
    utility.execute()
  File ""C:\Programme\django_src\django\core\management\__init__.py"", line 295, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File ""C:\Programme\django_src\django\core\management\base.py"", line 77, in run_from_argv
    self.execute(*args, **options.__dict__)
  File ""C:\Programme\django_src\django\core\management\base.py"", line 96, in execute
    output = self.handle(*args, **options)
  File ""C:\Programme\django_src\django\core\management\base.py"", line 178, in handle
    return self.handle_noargs(**options)
  File ""C:\Programme\django_src\django\core\management\commands\syncdb.py"", line 80, in handle_noargs
    cursor.execute(statement)
  File ""C:\Programme\django_src\django\db\backends\util.py"", line 19, in execute
    return self.cursor.execute(sql, params)
  File ""C:\Programme\Python25\lib\site-packages\MySQLdb\cursors.py"", line 166, in execute
    self.errorhandler(self, exc, value)
  File ""C:\Programme\Python25\lib\site-packages\MySQLdb\connections.py"", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1005, ""Can't create table '.\\db\\#sql-5b4_8.frm' (errno: 150)"")
}}}

MySQL is unable to add the constraint, because of the different types (integer, integer unsigned) used.

The problem goes back to [1970] where a foreign key pointing to an AutoField, a PositiveIntegerField or a PositiveSmallIntegerField always becomes an IntegerField."		closed	Database layer (models, ORM)	dev		fixed	1.0-blocker	sebastian@…	Accepted	1	0	1	1	0	0