Wrong syntax generated for foreign keys under MySQL/InnoDB 5.0.22

[anonymous]

Django uses the following syntax to define foreign keys under MySQL/InnoDB:

CREATE TABLE `system_script_queue` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `execution_time` datetime NOT NULL,
    `period` integer NOT NULL,
    `system_script_id` integer NOT NULL REFERENCES `system_script` (`id`)
);

Using that syntax, maybe 1/3 of my foreign key constraints are being ignored by the DB.

If I'm reading this tech note correctly (​http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html), that's the wrong syntax to use. The correct syntax is this one:

CREATE TABLE `system_script_queue` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `system_script_id` integer NOT NULL,
    `execution_time` datetime NOT NULL,
    `period` integer NOT NULL,
    foreign key (`system_script_id`) references `system_script` (`id`)
);

Using this syntax, all of my foreign key constraints seem to be successfully created.

[plesur@…]

(...just adding my email address....)

[James Bennett]

Have you double-checked that the foreign keys aren't being created later on as constraints? IIRC Django often creates the table without FOREIGN KEY constraints, and then adds those later in the run, once all the tables are created (this avoids problems caused by the order in which the tables are created).

[plesur@…]

Would that be evident in "manage.py sqlindexes <appname>"? I'm not seeing any constraints added there.

When I look at the SQL output by "manage.py sql <appname>", I see the first syntax used above; when I drop the table and recreate it with that foreign-key syntax, the table is created with no foreign key (it's consistent for a given table - if it fails once, it always fails).

When I drop the table and recreate it with the second syntax used above, it always succeeds.

[Joakim Sernbrant <serbaut@…>]

With mysql/innodb you need to use the CONSTRAINT... syntax, the first form is just a "comment" (this fact is hidden in the docs somewhere):

CREATE TABLE `test_bar` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `foo_id` integer NOT NULL REFERENCES `test_foo` (`id`),
    CONSTRAINT FOREIGN KEY (`foo_id`) REFERENCES `test_foo` (`id`)
);

I have a patch for this and some other db related things I will submit in the near future.

[anonymous]

Patch: Excellent, thank you! Will the patch be announced here, or should I be watching somewhere else?

[Joakim Sernbrant <serbaut@…>]

I think this patch should work.

[Joakim Sernbrant <serbaut@…>]

Note sure if this is compatible with all the other backends but my guess is that it is.

[plesur@…]

That seems to have done just what I needed - thanks again!

[Adrian Holovaty]

Fixed formatting in description.