DBAL-837: Cannot drop index needed in a foreign key constraint

[doctrinebot]

Jira issue originally created by user cmodijk:

I'm trying to remove an relation from an entity and i'm getting an error that it could not be executed. After testing it, it's missing the DROP FOREIGN KEY query.

The generated SQL is:
{quote}
DROP INDEX IDX_DCE815B325C79A8C ON moveMembers;
ALTER TABLE moveMembers DROP fkAccessId;
{quote}

When I use --force to execute it I get the following error:
{quote}
[Doctrine\DBAL\DBALException]
An exception occurred while executing 'DROP INDEX IDX_DCE815B325C79A8C ON moveMembers':

SQLSTATE[HY000]: General error: 1553 Cannot drop index 'IDX_DCE815B325C79A8C': needed in a foreign key constraint

[PDOException]
SQLSTATE[HY000]: General error: 1553 Cannot drop index 'IDX_DCE815B325C79A8C': needed in a foreign key constraint
{quote}

[doctrinebot]

• relates to DBAL-732: MySQL 5.6 - Cannot change column 'fkProjectId': used in a foreign key constraint

[doctrinebot]

Comment created by cmodijk:

Maybe related to DBAL-732?

[tomasfejfar]

Actual link for the related issue is #1958

[tomasfejfar]

Workaround for this would be to manually save the migration SQL and prefix it with foreign key drop. Then append FK's create query as well. Then run the whole thing. Unfortunately it's very error-prone. Nevertheless, it's the best workaround we've been able to come up with at the moment.

You can copy the FK's specification from SHOW CREATE TABLE table_name query.

[podorozhny]

ping

[Ocramius]

@podorozhny pinging is useless - open a PR with a failing test case.

[phpepe]

I've the same problem here.
Solved by sending a manual query before the migration:
ALTER TABLE [tablename] DROP FOREIGN KEY IDX_xxx ;
instead of generated SQL:
DROP INDEX IDX_xxx ON [tablename];

[morozov]

@phpepe is it related to #2929?

[phpepe]

hmm I don't think so, its not related with Primary key, its just foreign key column removal

[SCIF]

Hi @phpepe , may be this one?

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.