Using migrations to delete table with foreign key

问题:

Im trying to roll back my migrations.

My migrations file uses foreign keys like so

$table->foreign('user_one')->references('id')->on('users');
$table->foreign('user_two')->references('id')->on('users');

My down() function is like so

public function down()
{
    Schema::drop('pm_convo');
    Schema::drop('pm_convo_replys');
}

When i run my migrate command

php artisan migrate:refresh --seed --env=local

I am getting the following error

SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table `pm_convo`) 

Im not exactly sure what to do to fix this.

Edit:

I have tried: $table->dropForeign('pm_convo_user_one_foreign');

But im getting errors with that as well

回答1:

pm_convo_replys has a foreign key that references pm_convo, thus you cannot delete pm_convo first without violating a foreign key constraint in pm_convo_replys.

To delete both you need to delete pm_convo_replys first.

public function down()
{
    Schema::drop('pm_convo_replys');
    Schema::drop('pm_convo');
}

回答2:

I think this is a better way to do it:

public function down()
{
    DB::statement('SET FOREIGN_KEY_CHECKS = 0');
    Schema::dropIfExists('tableName');
    DB::statement('SET FOREIGN_KEY_CHECKS = 1');
}

回答3:

I also faced these kind of issues. Migration file order is the main issue here. The best way is to create migration files one by one. Main entities should be created first. Migration should be refreshed with every migrate file creation. (with php artisan migrate:refresh)

According to @abkrim and @Eric

public function down()
{
    Schema::disableForeignKeyConstraints();
    Schema::drop('tableName');
    Schema::enableForeignKeyConstraints();
}

回答4:

I think this is the most correct approach:

public function down()
{
    Schema::table('[table]', function (Blueprint $table) {
        $table->dropForeign('[table]_[column]_foreign');
        $table->dropColumn('[column]');
    });
}

回答5:

prefer to do it this way

    Schema::dropIfExists('tableNameChild');
    Schema::drop('tableNameParents');

回答6:

Important, this is for Laravel 5.4.

According to the docs

To drop a foreign key, you may use the dropForeign method. Foreign key constraints use the same naming convention as indexes. So, we will concatenate the table name and the columns in the constraint then suffix the name with "_foreign"

$table->dropForeign('posts_user_id_foreign');

Or, you may pass an array value which will automatically use the conventional constraint name when dropping:

$table->dropForeign(['user_id']);

I personally prefer the second one because of simplicity

回答7:

You can do that thing very easily if you added cascade on the foeign key when creating the table. If you did it, then you can remove the table very easily without something like this for PostgreSQL.

DB::statement("drop table tableName if exists cascade");

All you have to do is to put the statement for SQL table in the raw format.