Laravel 10添加分类触发1452外键约束违反错误求助

报错信息 #

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (laravel.categories, CONSTRAINT categories_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES categories (id))

问题场景 #

在Laravel 10中向categories表存储数据时触发上述错误，该表将自身主键id设为parent_id的外键，用于实现分类的层级结构。

现有迁移代码 #

Schema::create('categories', function (Blueprint $table) {
            $table->id();
            //$table->integer('parent_id')->default(0);
            $table->bigInteger('parent_id')->nullable()->unsigned()->default(0);
            $table->string('name');
            $table->string('slug');
            $table->text('description')->nullable();
            $table->string('image');
            $table->string('meta_title')->nullable();
            $table->string('meta_description')->nullable();
            $table->string('meta_keywords')->nullable();
            $table->integer('navbar_status')->nullable()->default(0);
            $table->integer('is_active')->nullable()->default(0);
            $table->timestamps();
            //$table->foreign('parent_id')->references('id')->on('categories');
        });
        Schema::table('categories', function (Blueprint $table) {
            $table->foreign('parent_id')->references('id')->on('categories');
        });

现有控制器存储代码 #

public function store(CategoryFormRequest $request){

        $data = $request->validated();

        $category = new Category();

        $slug = Str::of($data['name'])->slug('-');
        

        if ($request->hasFile('image')) {
            $file = $request->file('image');
            $category_image = date('ymd')."-".time()."c.".$file->getClientOriginalExtension();
            $file->move('uploads/category/', $category_image);
        }

        if($request->hasFile('banner_image')){
            $bnr_file = $request->file('banner_image');
            $cat_banner_image = date('ymd')."-".time().'b.'.$bnr_file->getClientOriginalExtension();
            $bnr_file->move('uploads/category/',$cat_banner_image);
        }

        $category->parent_id        = $data['parent_id'];
        $category->name             = $data['name'];
        $category->slug             = $slug;
        $category->description      = $data['description'];

        $category->image            = $category_image;
        $category->banner_image     = $cat_banner_image;

        $category->meta_title       = $data['meta_title'];
        $category->meta_description = $data['meta_description'];
        $category->meta_keywords    = $data['meta_keywords'];
        $category->navbar_status    = $request->navbar_status == true ? 1 : 0;
        $category->is_active        = $request->is_active == true ? 1 : 0;
        $category->is_featured      = $request->is_featured == true ? 1 : 0;
        

        $category->created_by       = Auth::user()->id;

        $category->save();

        return redirect()->route('category-create')->with('success', 'Category Added Successfully');
    }

报错原因 #

1. 迁移中给parent_id设置了default(0)，但Laravel自增主键从1开始，表中不存在id=0的记录，外键约束要求parent_id必须是已存在的id或NULL，默认值0违反约束。
2. 控制器直接赋值$data['parent_id']，若表单传入0或不存在的分类ID，会触发外键校验失败。

解决办法 #

1. 修正迁移文件

移除parent_id的默认值0，保留nullable属性，并添加删除父分类时的关联处理：

Schema::create('categories', function (Blueprint $table) {
    $table->id();
    // 移除default(0)，允许NULL代表顶级分类
    $table->bigInteger('parent_id')->nullable()->unsigned();
    $table->string('name');
    $table->string('slug');
    $table->text('description')->nullable();
    $table->string('image');
    $table->string('meta_title')->nullable();
    $table->string('meta_description')->nullable();
    $table->string('meta_keywords')->nullable();
    $table->integer('navbar_status')->nullable()->default(0);
    $table->integer('is_active')->nullable()->default(0);
    $table->timestamps();
});
Schema::table('categories', function (Blueprint $table) {
    // 添加onDelete('set null')，删除父分类时子分类自动设为顶级
    $table->foreign('parent_id')->references('id')->on('categories')->onDelete('set null');
});

2. 修正控制器逻辑

处理parent_id的有效值，确保传入的ID存在或设为NULL：

public function store(CategoryFormRequest $request){
    $data = $request->validated();

    $category = new Category();

    $slug = Str::of($data['name'])->slug('-');
    
    // 初始化图片变量，避免未上传时报错
    $category_image = null;
    if ($request->hasFile('image')) {
        $file = $request->file('image');
        $category_image = date('ymd')."-".time()."c.".$file->getClientOriginalExtension();
        $file->move('uploads/category/', $category_image);
    }

    $cat_banner_image = null;
    if($request->hasFile('banner_image')){
        $bnr_file = $request->file('banner_image');
        $cat_banner_image = date('ymd')."-".time().'b.'.$bnr_file->getClientOriginalExtension();
        $bnr_file->move('uploads/category/',$cat_banner_image);
    }

    // 处理parent_id：传入空值或0时设为NULL，同时验证ID是否存在
    $parentId = $data['parent_id'] ?? null;
    if ($parentId && !Category::find($parentId)) {
        return redirect()->back()->with('error', '指定的父分类不存在');
    }
    $category->parent_id = $parentId ?: null;

    $category->name             = $data['name'];
    $category->slug             = $slug;
    $category->description      = $data['description'];

    $category->image            = $category_image;
    $category->banner_image     = $cat_banner_image;

    $category->meta_title       = $data['meta_title'];
    $category->meta_description = $data['meta_description'];
    $category->meta_keywords    = $data['meta_keywords'];
    $category->navbar_status    = $request->navbar_status == true ? 1 : 0;
    $category->is_active        = $request->is_active == true ? 1 : 0;
    $category->is_featured      = $request->is_featured == true ? 1 : 0;
    
    $category->created_by       = Auth::user()->id;

    $category->save();

    return redirect()->route('category-create')->with('success', '分类添加成功');
}

3. 完善表单验证规则

在CategoryFormRequest中添加parent_id的验证，提前拦截无效值：

public function rules()
{
    return [
        'name' => 'required|string|max:255',
        // 验证parent_id要么为空，要么是已存在的分类ID
        'parent_id' => 'nullable|exists:categories,id',
        // 其他字段验证规则...
    ];
}

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