Code

Opened 8 years ago

Closed 6 years ago

#2130 closed defect (duplicate)

Foreign keys sometime not generated correctly

Reported by: anonymous Owned by: adrian
Component: Core (Other) Version: master
Severity: normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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.

Attachments (0)

Change History (5)

comment:1 Changed 8 years ago by anonymous

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

comment:2 Changed 8 years ago by mtredinnick

  • Resolution set to fixed
  • Status changed from new to closed

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.

comment:3 Changed 6 years ago by haavikko@…

  • Resolution fixed deleted
  • Status changed from closed to reopened

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;

comment:4 Changed 6 years ago by 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.

comment:5 Changed 6 years ago by ramiro

  • Resolution set to duplicate
  • Status changed from reopened to closed

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.