Foreign keys sometime not generated correctly

[anonymous]

I'm experiencing this problem using SQLite.

Consider the model:

class Fuel(models.Model):
	name = models.CharField(maxlength=50)

class Car(models.Model):
	fuel = models.ForeignKey(Fuel)

Running manage.py sqlall myproject correctly outputs:

BEGIN;
CREATE TABLE "pythag_fuel" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(50) NOT NULL
);
CREATE TABLE "pythag_car" (
    "id" integer NOT NULL PRIMARY KEY,
    "fuel_id" integer NOT NULL REFERENCES "pythag_fuel" ("id")
);
CREATE INDEX pythag_car_fuel_id ON "pythag_car" ("fuel_id");
COMMIT;

If I rename Fuel to FuelType:

class FuelType(models.Model):
	name = models.CharField(maxlength=50)

class Car(models.Model):
	fuel = models.ForeignKey(FuelType)

and rerun manage.py sqlall myproject, the following is output:

BEGIN;
CREATE TABLE "pythag_car" (
    "id" integer NOT NULL PRIMARY KEY,
    "fuel_id" integer NOT NULL
);
CREATE TABLE "pythag_fueltype" (
    "id" integer NOT NULL PRIMARY KEY,
    "name" varchar(50) NOT NULL
);
-- The following references should be added but depend on non-existant tables:
CREATE INDEX pythag_car_fuel_id ON "pythag_car" ("fuel_id");
COMMIT;

The foreign key relationship has not been created correcly in the output SQL.

[anonymous]

Conversly when using mysql the foreign key relationship is correctly created when using FuelType and incorrectly when using Fuel.

[Malcolm Tredinnick]

It looks like this has been corrected at some point since filing (possibly in [3182]). By changing the class names and the database backends, I can generate SQL where the reference is either inline in the table or as an "alter table" statement afterwards (it's this latter case that was failing in the second example above). Please do reopen if the problem reoccurs, but I believe this is fixed now.

[haavikko@…]

Reopening the ticket. I'm experiencing the same issue with mysql back-end, using SVN HEAD (r7030)

Here's an trimmed-down example that triggers the error:

class MyNode(models.Model):
    name = models.CharField(max_length=40)

class ZIntParam(models.Model):
    node = models.ForeignKey(MyNode)

Running "sqlall <appname>" produces a warning about non-existent table:

BEGIN;
CREATE TABLE `dws_mynode` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(40) NOT NULL
)
;
CREATE TABLE `dws_zintparam` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `node_id` integer NOT NULL
)
;
-- The following references should be added but depend on non-existent tables:
-- ALTER TABLE `dws_zintparam` ADD CONSTRAINT node_id_refs_id_35a7fb9a FOREIGN KEY (`node_id`) REFERENCES `dws_mynode` (`id`);
CREATE INDEX `dws_zintparam_node_id` ON `dws_zintparam` (`node_id`);
COMMIT;

But when ZIntParam is renamed to XIntParam, the warning goes away:

class MyNode(models.Model):
    name = models.CharField(max_length=40)

class XIntParam(models.Model):
    node = models.ForeignKey(MyNode)

The "sqlall" result now displays correctly! Note the changed order of "node" and "param" tables in SQL.

BEGIN;
CREATE TABLE `dws_xintparam` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `node_id` integer NOT NULL
)
;
CREATE TABLE `dws_mynode` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
    `name` varchar(40) NOT NULL
)
;
ALTER TABLE `dws_xintparam` ADD CONSTRAINT node_id_refs_id_5449fd14 FOREIGN KEY (`node_id`) REFERENCES `dws_mynode` (`id`);
CREATE INDEX `dws_xintparam_node_id` ON `dws_xintparam` (`node_id`);
COMMIT;

[Matti Haavikko]

The fix suggested by apramanik on django-users fixes the problem for me also:

> It seems to be fixed if I change sql_create django/core/management/
> sql.py to
>
>     for model in app_models:
>         output, references = sql_model_create(model, style,
> known_models)
>         final_output.extend(output)
>         for refto, refs in references.items():
>             pending_references.setdefault(refto, []).extend(refs)
>         #ADD
>         for known_model in known_models:
>
> final_output.extend(sql_for_pending_references(known_model, style,
> pending_references))
>         #END ADD
>         # Keep track of the fact that we've created the table for this
> model.
>         known_models.add(model)
>
> It didn't make sense to pass in model to sql_for_pending_references.

[Ramiro Morales]

Duplicate of #4193, it is being used as a hub for the issue with this kind of symptoms. Added a note there about the patch above