Doctrine schema update fails on foreign key

Question

I'm trying to do a schema update using the app/console doctrine:schema:update --force command, but Doctrine fails on the following part:

An exception occurred while executing 'DROP INDEX IDX_E98F2859A074D5D7 ON contract':                               
SQLSTATE[HY000]: General error: 1553 Cannot drop index 'IDX_E98F2859A074D5D7': needed in a foreign key constraint

This is trivial to resolve according to another SO question. The table has:

KEY `IDX_E98F2859A074D5D7` (`some_table_id`),
CONSTRAINT `FK_E98F2859A074D5D7` FOREIGN KEY (`some_table_id`) REFERENCES `some_table` (`id`)

So this can be resolved manually by dropping the matching constraint. But is there a way to do it automatically?

Answer 1

If you use the information schema, you can easily construct the necessary ALTER TABLE commands; the relevant tables are here: SCHEMA KEY_COLUMN_USAGE and STATISTICS.

Following is an example for a query which generates the DDL statements:

SELECT CONCAT('ALTER TABLE ',kcu.TABLE_NAME,' DROP FOREIGN KEY ', kcu.CONSTRAINT_NAME,';') AS ddl  
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu, INFORMATION_SCHEMA.STATISTICS stat  
WHERE stat.table_schema = 'your_schema' 
AND kcu.TABLE_NAME = stat.TABLE_NAME   
AND kcu.COLUMN_NAME = stat.COLUMN_NAME
AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
INTO OUTFILE '/tmp/ddl.sql';

And you can run it after reviewing it by running this statement:

SOURCE '/tmp/ddl.sql';

Above statement will look up constraint name and corresponding table name for every foreign key constraint, where an index exists on the same foreign key column in the same table. The result is stored in the file given by INTO OUTFILE.

Please review above statement carefully before running the generated ddl.