Why am I getting a P2003 Foreign Key Constraint Prisma Error?

Question

I am building a website with NextJS, Prisma, PostgreSQL, and Clerk for authentication.

I am using Clerk webhooks to send user data to my PostgreSQL database.

Following the Clerk guide, I created an API route to receive the webhook and post the data to my database using the following Prisma model:

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

So far, so good. Everything works great.

Now, as you can see from my User model above, I want to create a Prisma Favorite model and a Review model and connect those models to the User model with a relation.

Here is my Favorite model:

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])
}

This is where everything breaks. When I submit the API route for managing favorites in Postman on my localhost, I get the following error:

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

I don't understand this error. I don't have any idea what I need to do to fix it. It is especially frustrating because when I use Prisma Studio to create the favorite manually, it works beautifully and the User table shows that the User now has a Favorite associated with it.

Here is the API route for handling favorites, for your reference. (The only data the POST requires is a 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 });
  }
}

I have tried connecting these two tables in various ways and I can't seem to figure out why I keep getting this Foreign Key Constraint error. As I mentioned above, I am able to create the record in the Favorite table manually within Prisma Studio, and it works perfectly, but I cannot do it with the API route without getting this P2003 error.

UPDATE: I tried editing my Prisma models, following Prisma's documentation, and I am still getting an error.

New models:

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])
}

Error:

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

Answer 1

For anyone who comes to this later, I solved the problem. My updated implementation of my Prisma models worked great, but the issue was how I was calling the API in Postman. I was using a Bearer Token that was tied to a userId that no longer existed in Clerk (nor my database).

I had previously followed these steps to set up testing in my local env with Postman, but I forgot to go through the process again after I deleted the user from Clerk.

The result was an error from Prisma because my API route was trying to tie a new Favorite to a user ID that did not exist in the database.

I figured out the problem by logging the User ID the API was getting from Clerk auth() method. That's when I discovered that the user id Clerk was using no longer matched the user id of my most recently created account in Clerk. 👍🏻 (also 🤦🏼‍♂️)