Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#28849 closed Bug (fixed)

SQLite foreign key constraints are not repointed on model or field rename

Reported by: Simon Charette Owned by: nobody
Component: Migrations Version: 2.0
Severity: Release blocker Keywords:
Cc: Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Simon Charette)

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

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 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.

Change History (4)

comment:1 Changed 3 years ago by Simon Charette

Description: modified (diff)
Has patch: set
Patch needs improvement: set

Submitted a PR with a regression test for the table rename case.

Last edited 3 years ago by Simon Charette (previous) (diff)

comment:2 Changed 3 years ago by Tim Graham

Needs documentation: unset
Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:3 Changed 3 years ago by Simon Charette <charette.s@…>

Resolution: fixed
Status: newclosed

In 095c1aa:

Fixed #28849 -- Fixed referenced table and column rename on SQLite.

Thanks Ramiro for the input and Tim for the review.

comment:4 Changed 3 years ago by Simon Charette <charette.s@…>

In 31d318d1:

[2.0.x] Fixed #28849 -- Fixed referenced table and column rename on SQLite.

Thanks Ramiro for the input and Tim for the review.

Backport of 095c1aaa898bed40568009db836aa8434f1b983d from master

Note: See TracTickets for help on using tickets.
Back to Top