Laravel 11迁移报错：无法创建product_details表（外键约束错误）

问题描述 #

执行php artisan migrate时创建product_details表失败，报错：

2024_05_16_010921_create_product_details_table  45.49ms FAIL

Illuminate\Database\QueryException 

SQLSTATE[HY000]: General error: 1005 Can't create table `distrisalsas`.`product_details` 
(errno: 150 "Foreign key constraint is incorrectly formed") (Connection: mysql, SQL: 
alter table `product_details` add constraint `product_details_product_id_foreign` foreign 
key (`product_id`) references `products` (`id`) on delete set null on update cascade)      

at vendor\laravel\framework\src\Illuminate\Database\Connection.php:813
809▕                     $this->getName(), $query, $this->prepareBindings($bindings), $e
810▕                 );
811▕             }
812▕
➜ 813▕             throw new QueryException(     
814▕                 $this->getName(), $query, 
$this->prepareBindings($bindings), $e
815▕             );
816▕         }
817▕     }

1   vendor\laravel\framework\src\Illuminate\Database\Connection.php:571
PDOException::("SQLSTATE[HY000]: General error: 1005 Can't create table 
`distrisalsas`.`product_details` (errno: 150 "Foreign key constraint is incorrectly 
formed")")

2   vendor\laravel\framework\src\Illuminate\Database\Connection.php:571
PDOStatement::execute()

迁移代码 #

products表迁移 #

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('products', function (Blueprint $table) {
            $table->id();
            $table->string('product');
            $table->string('description');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('products');
    }
};

brands表迁移 #

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('brands', function (Blueprint $table) {
            $table->id();
            $table->string('brand');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('brands');
    }
};

references表迁移 #

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('references', function (Blueprint $table) {
            $table->id();
            $table->string('reference');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('references');
    }
};

product_details表迁移 #

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('product_details', function (Blueprint $table) {
            $table->id();            
            $table->foreignId('product_id')
            ->constrained()
            ->cascadeOnUpdate()
            ->nullOnDelete();
            $table->foreignId('brand_id')
            ->nullable()
            ->constrained()
            ->cascadeOnUpdate()
                ->nullOnDelete();
            $table->foreignId('reference_id')
                ->nullable()
                ->constrained()
                ->cascadeOnUpdate()
                ->nullOnDelete();       
            $table->decimal('price')
                ->nullable();
            $table->timestamps();
            });
    }


    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('product_details');
    }
};

解决方案 #

1. 修正product_id字段的可空属性 #

你给product_id设置了nullOnDelete()，但字段本身没有标记为nullable()。MySQL不允许将非空字段通过外键约束设置为null，因此需要给product_id添加nullable()：

$table->foreignId('product_id')
    ->nullable()
    ->constrained()
    ->cascadeOnUpdate()
    ->nullOnDelete();

2. 确认迁移文件执行顺序 #

Laravel按迁移文件名的时间戳顺序执行迁移，必须保证products、brands、references的迁移文件时间戳早于product_details的。例如，如果product_details的文件名是2024_05_16_010921_create_product_details_table，那另外三个表的迁移文件名时间戳应该更小（比如2024_05_16_010910_create_products_table）。如果顺序颠倒，创建product_details时父表还未存在，会触发外键约束错误。

3. 清理无效表并重新迁移 #

如果之前迁移失败导致数据库中存在不完整的表，先执行回滚：

php artisan migrate:rollback

或者手动删除数据库中的product_details表（如果存在），然后重新执行迁移：

php artisan migrate

内容的提问来源于stack exchange，提问作者erik_