#31207: Broken foreing key reference with multi-table inheritance (PostgreSQL,
Django 3.0)
-------------------------------------+-------------------------------------
Reporter: un.def | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.0
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
It looks like a regression in Django 3.0, possibly related to
`SchemaEditor.sql_create_column_inline_fk`.
Setting
`django.db.backends.postgresql.schema.DatabaseSchemaEditor.sql_create_column_inline_fk
= False` fixes the issue.
== Prerequisites
* Python 3.8.1
* PostgreSQL 9.6.12
* psycopg2 2.8.4
* Django 2.2.9 vs. 3.0.2
== Django 2.2.9
**Step 1**. Create models:
{{{#!python
class BaseModel(models.Model):
key = models.IntegerField(unique=True)
class ChildModel(BaseModel):
field = models.IntegerField()
class OtherModel(models.Model):
field = models.IntegerField()
}}}
**Step 2**. Generate initial migration:
{{{#!python
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
initial = True
dependencies = [
]
operations = [
migrations.CreateModel(
name='BaseModel',
fields=[
('id', models.AutoField(auto_created=True,
primary_key=True, serialize=False, verbose_name='ID')),
('key', models.IntegerField(unique=True)),
],
),
migrations.CreateModel(
name='OtherModel',
fields=[
('id', models.AutoField(auto_created=True,
primary_key=True, serialize=False, verbose_name='ID')),
('field', models.IntegerField()),
],
),
migrations.CreateModel(
name='ChildModel',
fields=[
('basemodel_ptr', models.OneToOneField(auto_created=True,
on_delete=django.db.models.deletion.CASCADE, parent_link=True,
primary_key=True, serialize=False, to='bugapp.BaseModel')),
('field', models.IntegerField()),
],
bases=('bugapp.basemodel',),
),
]
}}}
**Step 3**. Add foreign key from `OtherModel` to `ChildModel`:
{{{#!python
class OtherModel(models.Model):
field = models.IntegerField()
ref = models.ForeignKey(ChildModel, on_delete=models.CASCADE,
to_field='key', null=True)
}}}
**Step 4**. Generate migration:
{{{#!python
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
dependencies = [
('bugapp', '0001_initial'),
]
operations = [
migrations.AddField(
model_name='othermodel',
name='ref',
field=models.ForeignKey(null=True,
on_delete=django.db.models.deletion.CASCADE, to='bugapp.ChildModel',
to_field='key'),
),
]
}}}
**Step 5**. Inspect migration:
{{{#!sql
BEGIN;
--
-- Add field ref to othermodel
--
ALTER TABLE "bugapp_othermodel" ADD COLUMN "ref_id" integer NULL;
CREATE INDEX "bugapp_othermodel_ref_id_9cbf2643" ON "bugapp_othermodel"
("ref_id");
ALTER TABLE "bugapp_othermodel" ADD CONSTRAINT
"bugapp_othermodel_ref_id_9cbf2643_fk_bugapp_basemodel_key" FOREIGN KEY
("ref_id") REFERENCES "bugapp_basemodel" ("key") DEFERRABLE INITIALLY
DEFERRED;
COMMIT;
}}}
**Step 6**. Migrate:
{{{
Running migrations:
Applying bugapp.0002_othermodel_ref... OK
Applying sessions.0001_initial... OK
}}}
== Django 3.0.2
**Steps 1-4** are the same.
**Step 5** produces different SQL code:
{{{#!sql
BEGIN;
--
-- Add field ref to othermodel
--
ALTER TABLE "bugapp_othermodel" ADD COLUMN "ref_id" integer NULL
CONSTRAINT "bugapp_othermodel_ref_id_9cbf2643_fk_bugapp_basemodel_key"
REFERENCES "bugapp_childmodel"("key") DEFERRABLE INITIALLY DEFERRED; SET
CONSTRAINTS "bugapp_othermodel_ref_id_9cbf2643_fk_bugapp_basemodel_key"
IMMEDIATE;
CREATE INDEX "bugapp_othermodel_ref_id_9cbf2643" ON "bugapp_othermodel"
("ref_id");
COMMIT;
}}}
As you see, `ref_id` column references wrong table — `bugapp_childmodel`,
not `bugapp_basemodel`: `"ref_id" ... REFERENCES
"bugapp_childmodel"("key")`.
**Step 6** fails:
{{{
Applying bugapp.0001_initial... OK
Traceback (most recent call last):
File "<...>/python3.8/site-packages/django/db/backends/utils.py", line
86, in _execute
return self.cursor.execute(sql, params)
psycopg2.errors.UndefinedColumn: column "key" referenced in foreign key
constraint does not exist
The above exception was the direct cause of the following exception:
<...>
File "<...>/python3.8/site-packages/django/db/backends/utils.py", line
86, in _execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "key" referenced in foreign key
constraint does not exist
Applying bugapp.0002_othermodel_ref...
}}}
== Note
It does not happen if the foreign key has been created with the model and
not added later:
**Step 7**. Delete `0001_initial.py`
**Step 8**. Generate migration:
{{{#!python
# Generated by Django 3.0.2 on 2020-01-25 14:46
from django.db import migrations, models
import django.db.models.deletion
class Migration(migrations.Migration):
initial = True
dependencies = [
]
operations = [
migrations.CreateModel(
name='BaseModel',
fields=[
('id', models.AutoField(auto_created=True,
primary_key=True, serialize=False, verbose_name='ID')),
('key', models.IntegerField(unique=True)),
],
),
migrations.CreateModel(
name='ChildModel',
fields=[
('basemodel_ptr', models.OneToOneField(auto_created=True,
on_delete=django.db.models.deletion.CASCADE, parent_link=True,
primary_key=True, serialize=False, to='bugapp.BaseModel')),
('field', models.IntegerField()),
],
bases=('bugapp.basemodel',),
),
migrations.CreateModel(
name='OtherModel',
fields=[
('id', models.AutoField(auto_created=True,
primary_key=True, serialize=False, verbose_name='ID')),
('field', models.IntegerField()),
('ref', models.ForeignKey(null=True,
on_delete=django.db.models.deletion.CASCADE, to='bugapp.ChildModel',
to_field='key')),
],
),
]
}}}
**Step 9**. Inspect migration:
{{{#!sql
BEGIN;
--
-- Create model BaseModel
--
CREATE TABLE "bugapp_basemodel" ("id" serial NOT NULL PRIMARY KEY, "key"
integer NOT NULL UNIQUE);
--
-- Create model ChildModel
--
CREATE TABLE "bugapp_childmodel" ("basemodel_ptr_id" integer NOT NULL
PRIMARY KEY, "field" integer NOT NULL);
--
-- Create model OtherModel
--
CREATE TABLE "bugapp_othermodel" ("id" serial NOT NULL PRIMARY KEY,
"field" integer NOT NULL, "ref_id" integer NULL);
ALTER TABLE "bugapp_childmodel" ADD CONSTRAINT
"bugapp_childmodel_basemodel_ptr_id_407e3662_fk_bugapp_ba" FOREIGN KEY
("basemodel_ptr_id") REFERENCES "bugapp_basemodel" ("id") DEFERRABLE
INITIALLY DEFERRED;
ALTER TABLE "bugapp_othermodel" ADD CONSTRAINT
"bugapp_othermodel_ref_id_9cbf2643_fk_bugapp_basemodel_key" FOREIGN KEY
("ref_id") REFERENCES "bugapp_basemodel" ("key") DEFERRABLE INITIALLY
DEFERRED;
CREATE INDEX "bugapp_othermodel_ref_id_9cbf2643" ON "bugapp_othermodel"
("ref_id");
COMMIT;
}}}
Note that the reference is correct now: `("ref_id") REFERENCES
"bugapp_basemodel" ("key")`.
--
Ticket URL: <https://code.djangoproject.com/ticket/31207>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/049.798c34105cb42a9886dfb479facf6d3a%40djangoproject.com.
