Search code examples
sql-servercakephpforeign-keysmigrationphinx

CakePHP 3: can't remove foreign key column using MSSQL driver

I try to remove a column, which is a foreign key:

$table = $this->table('users');
$table->removeColumn('province_id');
$table->update();

Above gives DB error: The object 'users_province_id' is dependent on column 'province_id'. If I try to remove FK first:

$table = $this->table('users');
$table->removeIndex('province_id');
$table->removeColumn('province_id');
$table->update();

I get the same error. Using removeIndexByName:

$table = $this->table('users');
$table->removeIndexByName('users_province_id');
$table->removeColumn('province_id');
$table->update();

Also doesn't work. Thanks for any help.

Solution

  • Your code doesn't remove foreign key constraints, but only indexes and columns. To remove a foreign key constraint, you'd use the dropForeignKey() method, something along the lines of this:

    $table = $this->table('users');
$table
    ->dropForeignKey(
        // by columns used in the constraint, this would remove _all_
        // foreign key constraints on the table that are using the
        // `province_id` column
        'province_id',

        // optionally pass the name of the constraint in the second
        // argument instead, in order to remove only a specific single
        // constraint by its name
        'foreign_key_constraint_name'
    )
    ->removeIndex('province_id')
    ->removeColumn('province_id')
    ->update();

    See also