Laravel无法将外键deleted_by设为NULL的问题求助

尝试将外键字段deleted_by设为NULL，但该字段不允许保存NULL作为值。使用工厂生成Product时因无法设置该字段为NULL导致数据库填充失败，报错信息如下：

PDOException::("SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'deleted_by' cannot be null")

相关代码 #

Product 表迁移代码

Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->string('title', 2000);
    $table->string('slug', 2000);
    $table->string('image', 2000)->nullable();
    $table->string('image_mime')->nullable();
    $table->integer('image_size')->nullable();
    $table->longText('description')->nullable();
    $table->decimal('price', 10, 2);
    $table->foreignId('created_by')->references('id')->on('users')->nullable();
    $table->foreignId('updated_by')->references('id')->on('users')->nullable();
    $table->softDeletes();
    $table->foreignId('deleted_by')->references('id')->on('users')->nullable()->constrained();
    $table->timestamps();
});

User 表迁移代码

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

Product 工厂代码

public function definition()
{
    return [
        'title' => fake()->text(),
        'image' =>fake()->imageUrl(),
        'description' => fake()->realText(200),
        'price' => fake()->randomFloat(2, 2, 5),
        'created_at' => now(),
        'updated_at' => now(),
        'created_by' => 1,
        'updated_by' => 1,
        'deleted_by' => NULL,
    ];
}

解决办法 #

1. 修正迁移文件的外键定义顺序 #

Laravel中constrained()方法会自动处理外键约束，但如果先调用nullable()再调用constrained()，可能导致nullable设置被覆盖。调整方法顺序或拆分字段与外键定义：

方案A：调整方法调用顺序

// 原代码
$table->foreignId('deleted_by')->references('id')->on('users')->nullable()->constrained();

// 修改为
$table->foreignId('deleted_by')->constrained()->nullable();

方案B：拆分字段与外键定义

如果方案A无效，可拆分为两步定义：

// 先定义可为空的字段
$table->unsignedBigInteger('deleted_by')->nullable();
// 再单独添加外键约束
$table->foreign('deleted_by')->references('id')->on('users');

2. 更新数据库表结构 #

修改迁移文件后，需执行迁移更新表结构：

• 开发环境可直接刷新迁移（会清空数据）：

php artisan migrate:refresh

• 生产环境建议创建新迁移文件修改字段：

php artisan make:migration update_deleted_by_column_in_products_table

新迁移文件内容：

public function up()
{
    Schema::table('products', function (Blueprint $table) {
        $table->dropForeign(['deleted_by']);
        $table->unsignedBigInteger('deleted_by')->nullable()->change();
        $table->foreign('deleted_by')->references('id')->on('users');
    });
}

public function down()
{
    Schema::table('products', function (Blueprint $table) {
        $table->dropForeign(['deleted_by']);
        $table->unsignedBigInteger('deleted_by')->nullable(false)->change();
        $table->foreign('deleted_by')->references('id')->on('users');
    });
}

执行迁移：

php artisan migrate

3. 优化工厂定义 #

确认字段允许NULL后，工厂中可省略deleted_by字段（Laravel自动用NULL填充），或显式设置为小写null（符合PHP规范）：

public function definition()
{
    return [
        'title' => fake()->text(),
        'image' => fake()->imageUrl(),
        'description' => fake()->realText(200),
        'price' => fake()->randomFloat(2, 2, 5),
        'created_at' => now(),
        'updated_at' => now(),
        'created_by' => 1,
        'updated_by' => 1,
        'deleted_by' => null,
    ];
}

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