8

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?

Community
  • 1
  • 1
viraptor
  • 33,322
  • 10
  • 107
  • 191
  • you have a FK on which table and what exactly you are doing for which this error is thrown? – plain jane Aug 19 '13 at 07:36
  • @Sonali Hopefully provided the answer (doctrine:schema:update). The index is being removed by the schema update, but foreign key is not removed before it. – viraptor Aug 19 '13 at 10:02
  • i think you will have to drop the FK and add it again. Sorry if I am wrong... – plain jane Aug 19 '13 at 11:25
  • Sure, as I mentioned in the question - I can do that and it's doable. I'm looking for some alternative that's somehow automated (and that preferably doesn't involve grep/sed/awk on the `--dump-sql` output). Unless the answer is "doctrine just can't do that, get on with scripting the update" - that's fine with me too. – viraptor Aug 19 '13 at 11:31
  • It seems to be bug existing for MySQL. When I migrated to MariaDB problem vanished. – Daniel May 27 '17 at 11:17

1 Answers1

7

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.

Meetai.com
  • 6,622
  • 3
  • 31
  • 38
Flo Doe
  • 5,197
  • 2
  • 19
  • 18
  • Note that if you have keys that reference each other, deleting the first one deletes both, and you get an error when you try to delete the second one. – Jason Hanley Nov 12 '14 at 13:21
  • I copy and pasted the statements into phpmyadmin, but the first line already fails with an error: `#1025 - Error on rename of './webbase/aanmelding' to './webbase/#sql2-472-c5' (errno: 152) ` – rolandow May 18 '16 at 18:23