Check for foreign key existence in migrations

Laravel provides a couple of handy features to check for the existence of a table or column using the hasTable and hasColumn methods:

<?php

if (Schema::hasTable('users')) {
    //
}

if (Schema::hasColumn('users', 'email')) {
    //
}

However, sometimes you need to check the existence of foreign keys or indexes on tables, for example when you need to rename columns, in order to avoid annoying errors. Unfortunately Laravel doesn’t provide such methods out of the box, but you can easily leverage the doctrine/dbal library in order to get the current state of columns.

After that, you can easily get the Doctrine SchemaManager instance in your migration files, which provides additional abstraction methods on top of SQL. For example, you can easily build a function that returns the foreign keys of a table:

<?php

public function listTableForeignKeys($table)
{
    $conn = Schema::getConnection()->getDoctrineSchemaManager();

    return array_map(function($key) {
		return $key->getName();
    }, $conn->listTableForeignKeys($table));
}

And then you can use this function to check for foreign key existence, before dropping it:

<?php

$foreignKeys = $this->listTableForeignKeys('companies');

if(in_array('companies_created_by_foreign', $foreignKeys)) $table->dropForeign('companies_created_by_foreign');

Remember that Laravel concatenates the table name and the column in the constraint then it suffixes the name with “_foreign” for each foreign key, as you can see in this example.

Bonus: resolving “unknown database type enum requested” error

If you use the doctrine/dbal library to edit tables that contain ENUM columns, which Doctrine doesn’t support, you may encounter this error (even if you are not touching those columns in your migrations):

**Fatal error**: Uncaught exception 'Doctrine\DBAL\DBALException' with message 'Unknown database type enum requested, Doctrine\DBAL\Platforms\MySqlPlatform may not support it.'

Luckily for us, the fix is really easy, you just need to map MySQL ENUMs to Doctrine varchars. In Laravel the fix is the following:

<?php

$platform = Schema::getConnection()->getDoctrineSchemaManager()->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('enum', 'string');

Happy new year everyone!!!