为何使用Prisma时出现P2003外键约束错误？

背景 #

我使用NextJS、Prisma、PostgreSQL和Clerk构建带认证功能的网站，通过Clerk webhooks将用户数据同步到PostgreSQL数据库。最初的User模型如下：

model User {
  id        String     @id @unique
  favorites Favorite[]
  reviews   Review[]
}

此前功能一切正常，现在需要创建Favorite模型并与User模型建立关联，初始的Favorite模型定义如下：

model Favorite {
  id String @id @default(cuid())

  userId String @unique
  user   User   @relation(fields: [userId], references: [id])

  listingId String
  listing   Listing @relation(fields: [listingId], references: [id])
}

问题现象 #

在本地用Postman调用管理收藏的API路由时，收到P2003外键约束错误：

{
    "name": "PrismaClientKnownRequestError",
    "code": "P2003",
    "clientVersion": "5.9.1",
    "meta": {
        "modelName": "Favorite",
        "field_name": "Favorite_userId_fkey (index)"
    }
}

奇怪的是，在Prisma Studio中手动创建收藏记录完全正常，User表也能正常显示关联的Favorite数据。

处理收藏的API路由代码（POST仅需传入listingId） #

import prisma from "@/prisma/client";
import { favoriteSchema } from "@/schemas/validationSchemas";
import { auth } from "@clerk/nextjs";
import { NextRequest, NextResponse } from "next/server";

export async function POST(request: NextRequest) {
  const { userId } = auth();

  if (!userId)
    return NextResponse.json({ error: "Not authorized" }, { status: 401 });

  let body = await request.json();
  body = { userId, ...body };

  const validation = favoriteSchema.safeParse(body);

  if (!validation.success)
    return NextResponse.json(validation.error.format(), { status: 400 });

  const alreadyExists = await prisma.favorite.findFirst({
    where: {
      id: body.userId,
      listingId: body.listingId,
    },
  });

  if (alreadyExists) {
    try {
      await prisma.favorite.delete({
        where: {
          id: alreadyExists.id,
        },
      });
      return NextResponse.json(
        { success: "Favorite removed" },
        { status: 200 }
      );
    } catch (error) {
      return NextResponse.json(error, { status: 500 });
    }
  }

  try {
    const newFavorite = await prisma.favorite.create({
      data: {
        userId: body.userId,
        listingId: body.listingId,
      },
    });

    return NextResponse.json(newFavorite, { status: 201 });
  } catch (error) {
    return NextResponse.json(error, { status: 500 });
  }
}

尝试修改模型后仍报错 #

我调整了表关联方式，修改后的模型如下：

model User {
  id        String     @id @default(cuid())
  userId    String     @unique
  favorites Favorite[]
  reviews   Review[]
}

model Favorite {
  id String @id @default(cuid())

  clerkId String
  user    User   @relation(fields: [clerkId], references: [userId])

  listingId String
  listing   Listing @relation(fields: [listingId], references: [id])
}

但错误依旧，只是字段名变化：

{
    "name": "PrismaClientKnownRequestError",
    "code": "P2003",
    "clientVersion": "5.9.1",
    "meta": {
        "modelName": "Favorite",
        "field_name": "Favorite_clerkId_fkey (index)"
    }
}

请求帮助 #

恳请帮忙排查并解决这个P2003外键约束错误。

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