#31335: [mysql] Renaming a foreign key field that's also included in an index
fails
with "Cannot drop index 'foo_idx': needed in a foreign key constraint"
----------------------------------+------------------------------------
Reporter: Stephen Finucane | Owner: nobody
Type: Bug | Status: new
Component: Migrations | Version: 3.0
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+------------------------------------
Comment (by Perry Harrington):
There is a variation of this bug that goes to the core of the problem.
You have a model like this:
class Insect....
species = fk to model Species
has_wings = Boolean
You create the initial migration and it generates roughly this:
Table Insect
id
species_id
has_wings bool
PRIMARY_KEY(id)
KEY someconstraint (species_id)
CONSTRAINT someconstraint FK (species_id) REFERENCES species (id)
The CONSTRAINT keyword in the initial sql migration script will
automatically create an index named the same as the related name.
Django doesn't know about this magical key, since it didn't create it.
Next you add a new index with the following:
indexes = [
...Index(fields=['species_id','id','has_wings'])
]
The migration then creates something like this:
ALTER TABLE ADD INDEX someindex (species_id,id,has_wings)
Then MySQL silently deletes the supporting FK constraint index that it
created, replacing that single column index with the compound index you
just specified.
The *crucial* factor in MySQL's decision to drop the automagic index is
whether the FK is the first column in the index.
If the FK is the first column of the secondary index, MySQL drops it
because the automagic index is a duplicate.
Next, if you instead decide to change the index to:
indexes = [
...Index(fields=['id','has_wings'])
]
When you run the migration, Django generates a DROP index for the
secondary index, but because it's used as the FK constrain index, MySQL
refuses.
The same would happen if you changed the order of the columns in the index
(because column order is critical to access path):
indexes = [
...Index(fields=['id','species_id','has_wings'])
]
This may look like the same index, but it's not, and Django will drop the
old one and create the new one in the new order, generating another FK
error.
There are a few problems here:
- Django doesn't know about the shadow index and therefore its dependency
calculation does not take into account MySQL automagically creating and
dropping the FK dependent index
- Django DROPs indexes before creating new indexes
First, Django should create indexes before dropping indexes, this will
automatically resolve the dependency issue if the new index has the FK as
the first column of the index.
Second, if Django is creating a migration for a table where the FK was the
first column in a previously seen index, but that index is gone in the new
migration and there is no new index with the FK as the first column, then
it should automatically generate an FK constraint index with the name of
the FK constraint.
Put another way:
if old index set contained FK as first column, but new index set does not
have any index with FK as first column, generate a new KEY with just FK as
column.
Then you can drop old index and add new indexes.
But, belt and suspenders, you should add indexes before dropping indexes,
this can help cover corner cases.
--
Ticket URL: <https://code.djangoproject.com/ticket/31335#comment:5>
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/068.e654353f2c51a03da8717e14f3f9b6e3%40djangoproject.com.
