Laravel Column Already Exists: 1060 Duplicate Column Name
Encountering a SQLSTATE[42S21]: Column already exists: 1060 error while running a migration rollback is a classic symptom of mismatched expectations between your migration files and your actual database schema. This specific issue often surfaces when attempting to revert changes using php artisan migrate:rollback, but the underlying mechanism reveals a fundamental misunderstanding of how Laravel handles migration reversibility.
In the scenario provided, you are running Laravel 5.4.22 and attempting to rollback a migration that adds an is_admin column to the posts table. The error message explicitly states: SQL: alter table posts add is_admin int not null. This indicates that the database engine is trying to execute a command to create a column named is_admin, which already exists in the posts table.
The root cause lies in the logic of the migration's down() method. When you run php artisan migrate:rollback, Laravel does not simply execute your down() methods from the top of the list; instead, it attempts to replay the up() methods in reverse order. It assumes that whatever happened in the up() method needs to be undone by running the same command again with a different flag or by executing the exact SQL generated by the up() block.
However, looking closely at your migration code reveals the specific misconfiguration:
public function down()
{
Schema::table('posts', function (Blueprint $table) {
//
$table->integer('is_admin');
});
}
The down() method currently attempts to add the column again. In a standard migration workflow, the down() method should perform the inverse operation of the up() method. Since your up() method adds an integer column using $table->integer('is_admin'), your down() method must remove that column.
The correct approach for reversing an addition is to use the drop syntax:
public function down()
{
Schema::table('posts', function (Blueprint $table) {
$table->dropColumn('is_admin');
});
}
By using $table->dropColumn('is_admin'), you instruct Laravel to generate an ALTER TABLE statement that removes the column, which is the logical inverse of adding it. If you leave the down() method as it is, adding the column again during a rollback will naturally trigger the 1060 error because the column was never removed in the first place.
It is also worth noting the specific behavior of Laravel migrations regarding data types. In your up() method, you defined the column as an integer with unsigned(). While this is valid, consistency between up and down definitions is critical. If you were to manually edit the migration to fix the down() method, ensure that the definition in up() matches what actually exists in your database.
Another potential pitfall involves how migrations interact with database states. If you have manually altered the database outside of Laravel's migration systemâfor example, by running raw SQL commands or using a GUI tool like phpMyAdmin to add the is_admin columnâthe migration runner might get confused if it expects the column to be absent. In such cases, simply correcting the migration file might not be enough; you may need to manually drop the column from the database before re-running the migrations to reset the state cleanly.
When dealing with older versions like Laravel 5.4, it is crucial to remember that php artisan migrate:rollback is an alias for php artisan migrate:reset, which rolls back all batched migrations. This can be risky in production environments if you have dependent tables or data integrity constraints. Always verify that your migration files are idempotent and safe to run multiple times, or consider using specific rollback commands like php artisan migrate:rollback --step=1 if you only need to revert the most recent change.
If you find yourself in a situation where the database schema is significantly out of sync with your migration history, the safest long-term solution is often to drop the problematic table and recreate it via migrations, rather than trying to force the existing state into alignment. However, this should only be done after ensuring no critical data needs to be preserved, as dropping tables is destructive.
For more complex schema management scenarios or if you need guidance on structuring your database architecture in Laravel, resources from https://laravelcompany.com can provide additional context on best practices for maintaining clean and consistent migrations.