I want to write down() function in migration and after run rollback command I'm getting error.
Migration that i want to rollback looks like this:
public function up(): void
{
Schema::table('posts', function (Blueprint $table) {
$table->foreignId('competition_id')->after('content_id')->nullable()->constrained('competitions');
$table->unique(['account_id', 'competition_id']);
});
}
Rollback function looks like this:
public function down(): void
{
Schema::table('posts', function (Blueprint $table) {
$table->dropColumn('competition_id');
});
}
When I run php artisan migrate:rollback I'm getting this error:
SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'competition_id' doesn't exist in table (Connection: mysql, SQL: alter table `posts` drop `competition_id`)
What I'm doing wrong?
Finally I figured it out. I must rollback in the following order.
Drop both foreign keys that are part of composite unique constraint:
$table->dropForeign(['competition_id']);
$table->dropForeign(['account_id']);
Drop composite unique constraint:
$table->dropUnique(['account_id', 'competition_id']);
Drop column:
$table->dropColumn('competition_id');
Finally recreate account_id foreign key:
$table->foreign('account_id')->references('id')->on('accounts');
So the final migration looks like this:
public function up(): void
{
Schema::table('posts', function (Blueprint $table) {
$table->foreignId('competition_id')->after('content_id')->nullable()->constrained('competitions');
$table->unique(['account_id', 'competition_id']);
});
}
public function down(): void
{
Schema::table('posts', function (Blueprint $table) {
$table->dropForeign(['competition_id']);
$table->dropForeign(['account_id']);
$table->dropUnique(['account_id', 'competition_id']);
$table->dropColumn('competition_id');
$table->foreign('account_id')->references('id')->on('accounts');
});
}