Laravel 9 表间外键约束创建失败问题求助

问题描述 #

执行Laravel迁移时出现以下错误：

SQLSTATE[HY000]: General error: 1005 Can't create table `invoices`.`invoice_attachments` (errno: 150 "Foreign key constraint is incorrectly formed") (SQL: alter table `invoice_attachments` add constraint `invoice_attachments_invoice_id_foreign` foreign key (`invoice_id`) references `invoices` (`id`) on delete cascade)

  at E:\Laravel\invoices\vendor\laravel\framework\src\Illuminate\Database\Connection.php:759
    755▕         // If an exception occurs when attempting to run a query, we'll format the error
    756▕         // message to include the bindings with SQL, which will make this exception a
    757▕         // lot more helpful to the developer instead of just the database's errors.
    758▕         catch (Exception $e) {
  ➜ 759▕             throw new QueryException(
    760▕                 $query, $this->prepareBindings($bindings), $e
    761▕             );


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

  2   E:\Laravel\invoices\vendor\laravel\framework\src\Illuminate\Database\Connection.php:544
      PDOStatement::execute()

需求为创建invoice_attachments表，通过invoice_id关联invoices表。

迁移代码 #

invoices表迁移代码 #

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        if(!Schema::hasTable('invoices')) {
            Schema::create('invoices', function (Blueprint $table) {

                $table->id();
                $table->string('invoice_number', 50);
                $table->date('invoice_Date')->nullable();
                $table->date('Due_date')->nullable();
                $table->string('product', 50);
                $table->bigInteger('section_id')->unsigned();
                $table->foreign('section_id')->references('id')->on('sections')->onDelete('cascade');
                $table->decimal('Amount_collection', 8, 2)->nullable();;
                $table->decimal('Amount_Commission', 8, 2);
                $table->decimal('Discount', 8, 2);
                $table->decimal('Value_VAT', 8, 2);
                $table->string('Rate_VAT', 999);
                $table->decimal('Total', 8, 2);
                $table->string('Status', 50);
                $table->integer('Value_Status');
                $table->text('note')->nullable();
                $table->date('Payment_Date')->nullable();
                $table->softDeletes();
                $table->timestamps();
            });
        }
    }

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

invoice_attachments表迁移代码 #

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        if (!Schema::hasTable('invoice_attachments')) {
            Schema::create('invoice_attachments', function (Blueprint $table) {
                $table->id();
                $table->string('file_name', 999);
                $table->string('invoice_number', 50);
                $table->string('Created_by', 999);
                $table->unsignedBigInteger('invoice_id')->unsigned();
                //$table->foreign('invoice_id')->references('id')->on('sections')->onDelete('cascade');
                $table->foreign('invoice_id')->references('id')->on('invoices')->onDelete('cascade');
                $table->timestamps();
            });
        }
    }

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

迁移文件顺序 #

已尝试的方法 #

将invoices表的id改为bigIncrement，无效。

问题更新 #

解决invoice_attachments的问题后，invoice_details表出现同样的外键约束错误，其迁移代码如下：

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        if (!Schema::hasTable('invoice_details')) {

            Schema::create('invoice_details', function (Blueprint $table) {

                $table->id();
                $table->string('invoice_number', 50);
                $table->foreignId('invoice_id')->references('id')->on('invoices')->constrained()->cascadeOnUpdate()->cascadeOnDelete();
                $table->string('product', 50);
                $table->string('Section', 999);
                $table->string('Status', 50);
                $table->integer('Value_Status');
                $table->date('Payment_Date')->nullable();
                $table->text('note')->nullable();
                $table->string('user', 300);
                $table->timestamps();
            });
        }
    }

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

解决办法 #

1. 修正迁移执行顺序（核心问题） #

Laravel迁移按文件名的时间戳从小到大执行，从迁移顺序图可见，invoice_attachments和invoice_details的迁移文件时间戳早于invoices表，导致创建子表时父表invoices还未生成，触发外键约束错误。

操作步骤：

• 修改invoice_attachments和invoice_details的迁移文件名前缀，将时间戳改为晚于invoices表迁移文件的时间（比如invoices是2024_05_10开头，子表就改成2024_05_11开头）。
• 清空数据库中已创建的相关表（或直接删除数据库重新创建），然后重新执行迁移命令：php artisan migrate。

2. 规范外键字段写法 #

• 对于invoice_attachments表，将$table->unsignedBigInteger('invoice_id')->unsigned();改为Laravel推荐的快捷写法：

  $table->foreignId('invoice_id')->constrained('invoices')->onDelete('cascade');
  

  该写法会自动创建unsignedBigInteger类型的字段，并正确关联父表，避免手动写类型出错。
• 对于invoice_details表，$table->foreignId('invoice_id')->references('id')->on('invoices')->constrained()->cascadeOnUpdate()->cascadeOnDelete();可以简化为：

  $table->foreignId('invoice_id')->constrained()->cascadeOnUpdate()->cascadeOnDelete();
  

  因为constrained()默认会关联同名字段加_id对应的父表（这里就是invoices表的id字段）。

3. 移除冗余的表存在性检查（可选） #

如果是新环境开发，没必要保留if(!Schema::hasTable('xxx'))判断，直接创建表即可，避免因历史表存在导致迁移逻辑异常。

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