Foreign key constraint error when syncing unmigrated models that relate to migrated models

[Collin Anderson]

Test app: ​https://github.com/collinanderson/testcustomuser/commit/544eca4061f068d90e4c17a4af99a982bd6c641f

Traceback: ​https://gist.github.com/collinanderson/9df14bbeed50c0000ea6

works fine on 1.7
works on sqlite

[Tim Graham]

I ran into a similar problem upgrading djangoproject.com to 1.8 because django-registration doesn't have migrations. My solution was to add migrations for it. As migrations will become compulsory in 1.9, I believe expanding ​the warning about having unmigrated apps depend on migrated apps might be sufficient to address this rather than add more code that's applicable to 1.8 only.

[Tim Graham]

By the way, this affects any database that enforces foreign key constraints, not just MySQL.

[Collin Anderson]

Yes.

mysql -e'drop database testcustomuser; create database testcustomuser character set utf8'
PYTHONPATH=~/django1.7 python3 ./manage.py migrate
mysqldump testcustomuser | grep admin_log_user
  CONSTRAINT `django_admin_log_user_id_53acb657f00068f0_fk_app_myuser_id` FOREIGN KEY (`user_id`) REFERENCES `app_myuser` (`id`),

[Markus Holtermann]

On 1.8 this is the SQL run by the syncdb code (last query fails):

CREATE TABLE `app_myuser` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `password` varchar(128) NOT NULL, `last_login` datetime(6) NULL, `is_superuser` bool NOT NULL, `username` varchar(30) NOT NULL UNIQUE, `first_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `email` varchar(254) NOT NULL, `is_staff` bool NOT NULL, `is_active` bool NOT NULL, `date_joined` datetime(6) NOT NULL) None
SELECT engine FROM information_schema.tables WHERE table_name = %s ['app_myuser']
CREATE TABLE `app_myuser_groups` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `myuser_id` integer NOT NULL, `group_id` integer NOT NULL, UNIQUE (`myuser_id`, `group_id`)) None
SELECT engine FROM information_schema.tables WHERE table_name = %s ['app_myuser_groups']
CREATE TABLE `app_myuser_user_permissions` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `myuser_id` integer NOT NULL, `permission_id` integer NOT NULL, UNIQUE (`myuser_id`, `permission_id`)) None
SELECT engine FROM information_schema.tables WHERE table_name = %s ['app_myuser_user_permissions']
    Running deferred SQL...
ALTER TABLE `app_myuser_groups` ADD CONSTRAINT `app_myuser_groups_myuser_id_2b41e8461df0328_fk_app_myuser_id` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`) None
ALTER TABLE `app_myuser_groups` ADD CONSTRAINT `app_myuser_groups_group_id_7a5f5bcafc6997d9_fk_auth_group_id` FOREIGN KEY (`group_id`) REFERENCES `au

On 1.7 the respective SQL is the following (no failures):

CREATE TABLE `app_myuser_groups` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `myuser_id` integer NOT NULL, `group_id` integer NOT NULL, UNIQUE (`myuser_id`, `group_id`)); None
CREATE TABLE `app_myuser_user_permissions` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `myuser_id` integer NOT NULL, `permission_id` integer NOT NULL, UNIQUE (`myuser_id`, `permission_id`)); None
CREATE TABLE `app_myuser` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `password` varchar(128) NOT NULL, `last_login` datetime NOT NULL, `is_superuser` bool NOT NULL, `username` varchar(30) NOT NULL UNIQUE, `first_name` varchar(30) NOT NULL, `last_name` varchar(30) NOT NULL, `email` varchar(75) NOT NULL, `is_staff` bool NOT NULL, `is_active` bool NOT NULL, `date_joined` datetime NOT NULL); None
ALTER TABLE `app_myuser_groups` ADD CONSTRAINT `myuser_id_refs_id_08272aba` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`); None
ALTER TABLE `app_myuser_user_permissions` ADD CONSTRAINT `myuser_id_refs_id_becb7d62` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`); None
CREATE INDEX `app_myuser_groups_f1d9e869` ON `app_myuser_groups` (`myuser_id`); None
CREATE INDEX `app_myuser_groups_5f412f9a` ON `app_myuser_groups` (`group_id`); None
CREATE INDEX `app_myuser_user_permissions_f1d9e869` ON `app_myuser_user_permissions` (`myuser_id`); None
CREATE INDEX `app_myuser_user_permissions_83d7f98b` ON `app_myuser_user_permissions` (`permission_id`); None

However, looking at the constraints for app_myuser_groups this reveals a missing constraint to auth_group.

CREATE TABLE `app_myuser_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `myuser_id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `myuser_id` (`myuser_id`,`group_id`),
  KEY `app_myuser_groups_f1d9e869` (`myuser_id`),
  KEY `app_myuser_groups_5f412f9a` (`group_id`),
  CONSTRAINT `myuser_id_refs_id_08272aba` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

In other words, 1.7 seems to silently ignore to add constraints to missing tables.

See the docs as well: ​https://docs.djangoproject.com/en/1.7/topics/migrations/#unmigrated-dependencies

[Markus Holtermann]

Hint: the constraints to e.g. auth_group are created if auth has been migrated before:

$ python manage.py migrate auth
$ python manage.py migrate
$ echo "SHOW CREATE TABLE app_myuser_groups;" | mysql -u django -p django
Enter password:
Table   Create Table
app_myuser_groups       CREATE TABLE `app_myuser_groups` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `myuser_id` int(11) NOT NULL,\n  `group_id` int(11) NOT NULL,\n  PRIMARY KEY (`id`),\n  UNIQUE KEY `myuser_id` (`myuser_id`,`group_id`),\n  KEY `app_myuser_groups_f1d9e869` (`myuser_id`),\n  KEY `app_myuser_groups_5f412f9a` (`group_id`),\n  CONSTRAINT `group_id_refs_id_58abbaa5` FOREIGN KEY (`group_id`) REFERENCES `auth_group` (`id`),\n  CONSTRAINT `myuser_id_refs_id_08272aba` FOREIGN KEY (`myuser_id`) REFERENCES `app_myuser` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8

[Collin Anderson]

Cool. Thanks for looking into it.