Problem with foreign keys and order of queries returned by AbstractPlatform::getAlterSchemaSQL

[h1k3r]

Bug Report

Q	A
Version	4.2.1
Previous Version if the bug is a regression	3.9.3

When deleting a table with a foreign key reference, queries returned by AbstractPlatform::getAlterSchemaSQL can only be executed only when checking foreign keys is disabled.

Summary

During update from doctrine/dbal: 3.9 to doctrine/dbal: 4.2 I've faced a problem with an order of sql queries that are generated by AbstractPlatform::getAlterSchemaSQL.

I need to delete a table that is being referenced by a foreign key. I am using the next sequence of commands:

$toSchema->getTable('blog')->removeForeignKey('fk_blog_category');
$toSchema->getTable('blog')->dropColumn('category_id');
$toSchema->dropTable('category');

$diff = $schemaManager->createComparator()->compareSchemas($fromSchema, $toSchema);
$queries = $conn->getDatabasePlatform()->getAlterSchemaSQL($diff);

The resulting SQLs can be executed in the returned order only when checking foreign keys is disabled. I am not sure that this is an intended behavior.

Current behavior

The $queries array in the snippet above will end up containing next queries:

DROP TABLE category
ALTER TABLE blog DROP FOREIGN KEY fk_blog_category
ALTER TABLE blog DROP category_id

Expected behavior

When using version 3.9 $queries array contains next queries:

ALTER TABLE blog DROP FOREIGN KEY fk_blog_category
DROP TABLE category
ALTER TABLE blog DROP category_id

I believe behavior changed with the deletion of handling of orphaned keys in https://github.com/doctrine/dbal/commit/0c08553e6ab6aaac2e2a939380bd47b4b667cb7a

How to reproduce

You can find repository with a simple app reproducing this behavior here. It works both with 3.9 and 4.2 versions.