Well I am not really sure whom to fault on that. But I am only super glad that I did double check the database constraints.
Django has this super cool feature which lets you create the tables in the database — “syncdb”. When you are using a MySQL database this is a part of what you will end up getting:
CREATE TABLE `auth_user_groups` (
`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
`user_id` integer NOT NULL REFERENCES `auth_user` (`id`),
`group_id` integer NOT NULL REFERENCES `auth_group` (`id`),
UNIQUE (`user_id`, `group_id`)
That sounds like a perfectly valid SQL statement and tables get created perfectly fine. The lines in bold indicate that there is a foreign key to another table’s column from the current table. But when I run syncdb the foreign key constraints just won’t get created on the database. I spent a whole evening trying to figure out what I might be doing wrong since everythign seemed to be perfectly alright. Finally I found the culprit after reading through MySQL Documentation.
REFERENCES specifications where the references are defined as part of the column specification are silently ignored by
InnoDB. InnoDB only accepts
REFERENCES clauses when specified as part of a separate
FOREIGN KEY specification.
I am not really sure whose fault it should be. As a user I would either want
a) MySQL to throw errors.
b) Django to generate the format that MySQL would really use and not just ignore.
I am not sure how many django developers who use MySQL really know that this is the case. If not for me using mysql in linux which defaults to using myISAM and my histrionics to make the app use InnoDB, I probably wouldnt have checked or delved deep into this.
I need to check if there is a ticket in django already for this. If not maybe this is probably the first patch I can work on for django.
Anyways, I ended up using sqlall to generate the sql statements and store it in a file and parse the file to create the alter table commands. I have put this as a django snippet in case anyone is as lazy as me to write the alter table commands manually.