#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
----------------------------------+------------------------------------
Changes (by Simon Charette):
* type: Uncategorized => Bug
* component: Uncategorized => Migrations
* stage: Unreviewed => Accepted
Comment:
Looks like MySQL will be ''smart'' enough to silently drop the index on
`article_id` when `comment_list_covering_idx` is created but decides to do
otherwise when the latter is dropped.
From [https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-
keys.html MySQL's docs]
> MySQL requires indexes on foreign keys and referenced keys so that
foreign key checks can be fast and not require a table scan. In the
referencing table, there must be an index where the foreign key columns
are listed as the first columns in the same order. Such an index is
created on the referencing table automatically if it does not exist.
**This index might be silently dropped later if you create another index
that can be used to enforce the foreign key constraint**. index_name, if
given, is used as described previously.
So it looks like MySQL's schema editor `remove_index` logic needs to
handle that case by manually creating an index for `index.fields[0]` if
it's a foreign key.
FWIW it looks like the `AddIndex` and `RemoveIndex` here are completely
unnecessary and that the auto-detector and `RenameField` field operation
[https://github.com/django/django/blob/aee0bebc2faf9c6de8211b05d5f1281dc016084f/django/db/migrations/autodetector.py#L1093-L1101
should be taught] about avoiding it
[https://github.com/django/django/blob/aee0bebc2faf9c6de8211b05d5f1281dc016084f/django/db/migrations/operations/fields.py#L329-L336
just like they do] with `index_together`. That should be tracked in a
different ''optimization'' ticket though as this issue is still relevant
in cases where an `Index` is explicitly removed.
--
Ticket URL: <https://code.djangoproject.com/ticket/31335#comment:2>
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.0a6c01f5dac506d5c837b29d57809100%40djangoproject.com.
