If you create foreign keys in your migrations, there may be a situation that the table is created successfully, but the foreign key fails. Then your migration is “half successful”, and if you re-run it after the fix, it will say “Table already exists”. What to do?
The Problem: Explained
First, let me explain the problem in detail. Here’s an example.
Schema::create('teams', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->foreignId('team_league_id')->constrained();
$table->timestamps();
});
The code looks good, right? Now, what if the referenced table “team_leagues” doesn’t exist? Or maybe it’s called differently? Then you will see this error in the Terminal:
2023_06_05_143926_create_teams_table ..................................................................... 20ms FAIL
Illuminate\Database\QueryException
SQLSTATE[HY000]: General error: 1824 Failed to open the referenced table 'team_leagues'
(Connection: mysql, SQL: alter table `teams` add constraint `teams_team_league_id_foreign` foreign key (`team_league_id`) references `team_leagues` (`id`))
But that is only part of the problem. So ok, you realized that the referenced table is called “leagues” and not “team_leagues”. Possible fix options:
- Either rename the field of “team_league_id” to just “league_id”
- Or, specify the table
->constrained('leagues')
But the real problem now is the state of the database:
- The table
teams is already created
- But the foreign key to leagues has failed!
This means there’s no record of this migration success in the “migrations” Laravel system DB table.
Now, the real problem: if you fix the error in the same migration and just run php artisan migrate, it will say, “Table already exists”.
2023_06_05_143926_create_teams_table ...................................................................... 3ms FAIL
Illuminate\Database\QueryException
SQLSTATE[42S01]: Base table or view already exists:
1050 Table 'teams' already exists
(Connection: mysql, SQL: create table `teams` (...)
So should you create a new migration? Rollback? Let me explain my favorite way of solving this.
Solution: Schema::hasTable() and Separate Foreign Key
You can re-run the migration for already existing tables and ensure they would be created only if they don’t exist with the Schema::hasTable() method.
But then, we need to split the foreignId() into parts because it’s actually a 2-in-1 method: it creates the column (which succeeded) and the foreign key (which failed).
So, we rewrite the migration into this:
if (! Schema::hasTable('teams')) {
Schema::create('teams', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->unsignedBigInteger('team_league_id');
$table->timestamps();
});
}
// This may be in the same migration file or in a separate one
Schema::table('teams', function (Blueprint $table) {
$table->foreign('team_league_id')->constrained('leagues');
});
Now, if you run php artisan migrate, it will execute the complete migration(s) successfully.
Of course, an alternative solution would be to go and manually delete the teams table via SQL client and re-run the migration with the fix, but you don’t always have access to the database if it’s remote. Also, it’s not ideal to perform any manual operations with the database if you use migrations. It may be ok on your local database, but this solution above would be universal for any local/remote databases.
