#28849: SQLite foreign key constraints are not repointed on model or field
rename
---------------------------------+------------------------------------
Reporter: Simon Charette | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 2.0
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 1
Needs tests: 0 | Patch needs improvement: 1
Easy pickings: 0 | UI/UX: 0
---------------------------------+------------------------------------
Changes (by Simon Charette):
* needs_better_patch: 0 => 1
* has_patch: 0 => 1
Old description:
> SQLite foreign key constraints are not automatically repointed on table
> or column renames which is going to cause a issues to projects who
> previously renamed a model or a column referenced by another model when
> they migrate to Django 2.0 which start enforcing them.
>
> e.g. of a SQLite3 session
>
> {{{#!sql
> SQLite version 3.11.0 2016-02-15 17:29:24
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> CREATE TABLE foo ("id" integer NOT NULL PRIMARY KEY
> AUTOINCREMENT);
> sqlite> CREATE TABLE bar ("id" integer NOT NULL PRIMARY KEY
> AUTOINCREMENT, "foo_id" integer NOT NULL REFERENCES "foo" ("id")
> DEFERRABLE INITIALLY DEFERRED);
> sqlite> ALTER TABLE foo RENAME TO renamed;
> sqlite> .schema bar
> CREATE TABLE bar ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
> "foo_id" integer NOT NULL REFERENCES "foo" ("id") DEFERRABLE INITIALLY
> DEFERRED); # It still points to "foo"
> sqlite> INSERT INTO bar (foo_id) VALUES (999);
> sqlite> PRAGMA foreign_keys = ON; # From Django 2.0+
> sqlite> INSERT INTO bar (foo_id) VALUES (999);
> Error: no such table: main.foo
> }}}
>
> Since we're already documenting that
> [https://docs.djangoproject.com/en/2.0/releases/2.0/#foreign-key-
> constraints-are-now-enabled-on-sqlite users should remake their tables]
> it should alleviate the issue but we should also sure to:
>
> 1. Document that tables that have been previously renamed (or had a
> column referenced by a `ForeignKey(to_field)` renamed) ''must'' be
> rebuilt.
> 2. Adjust SQLite's schema editor to remake tables referencing a renamed
> table or one that is remade to simulate a column rename to prevent this
> from happening from now on.
>
> This was discovered while working on fixing #25817.
New description:
SQLite foreign key constraints are not automatically repointed on table or
column renames which is going to cause a issues to projects who previously
renamed a model or a column referenced by another model when they migrate
to Django 2.0 which start enforcing them.
e.g. of a SQLite3 session
{{{#!sql
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE foo ("id" integer NOT NULL PRIMARY KEY
AUTOINCREMENT);
sqlite> CREATE TABLE bar ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"foo_id" integer NOT NULL REFERENCES "foo" ("id") DEFERRABLE INITIALLY
DEFERRED);
sqlite> ALTER TABLE foo RENAME TO renamed;
sqlite> .schema bar
CREATE TABLE bar ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"foo_id" integer NOT NULL REFERENCES "foo" ("id") DEFERRABLE INITIALLY
DEFERRED); -- It still points to "foo"
sqlite> INSERT INTO bar (foo_id) VALUES (999);
sqlite> PRAGMA foreign_keys = ON; -- From Django 2.0+
sqlite> INSERT INTO bar (foo_id) VALUES (999);
Error: no such table: main.foo
}}}
Since we're already documenting that
[https://docs.djangoproject.com/en/2.0/releases/2.0/#foreign-key-
constraints-are-now-enabled-on-sqlite users should remake their tables] it
should alleviate the issue but we should also sure to:
1. Document that tables that have been previously renamed (or had a column
referenced by a `ForeignKey(to_field)` renamed) ''must'' be rebuilt.
2. Adjust SQLite's schema editor to remake tables referencing a renamed
table or one that is remade to simulate a column rename to prevent this
from happening from now on.
This was discovered while working on fixing #25817.
--
Comment:
Submitted a [https://github.com/django/django/pull/9389 PR] with a
regression for the table rename case.
--
Ticket URL: <https://code.djangoproject.com/ticket/28849#comment:1>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/067.175c898aab27cc360798099339b1aee7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
