Prisma•3mo ago

Getting foreign key error even though foreign row exists

I have two tables connected by a foreign key constraint. My foo table has a foo_uuid column, and my bar table references it via a foo_uuid column. I am trying to call this method:

prismaClient.bar.create({
  data: {
    foo_uuid: 123,
    other_bar_field: "whatever"
  }
})

prismaClient.bar.create({
  data: {
    foo_uuid: 123,
    other_bar_field: "whatever"
  }
})

I have DBeaver open and can see that there is a row in the foo table with foo_uuid = 123, but when I run the create method I get an error: "Foreign key constraint violated". It has code P2003 and says the error is on the foo_uuid foreign key constraint. Thing is, not only does the foo exist, but I can also insert this row into bar using DBeaver without any errors. What's going on? Is there some additional argument that's needed for the create call when a foreign key exists?

3 Replies

Prisma AI Help•3mo ago

You've gone full bot mode! Let me fetch that AI answer for you ASAP. The error code P2003 ("Foreign key constraint failed") in Prisma means the value you are providing for the foreign key (foo_uuid) does not match an existing primary key in the referenced table, according to Prisma's query engine. However, you mention that: - The row exists in foo (foo_uuid = 123). - You can insert the row in bar via DBeaver without error. - The error only occurs via Prisma. Here are some possible causes and troubleshooting steps, based on the knowledge sources: 1. Data Type Mismatch: Prisma is strict about data types. If your foo_uuid column in the database is, for example, an integer, but Prisma's schema defines it as a string (or vice versa), Prisma may not match the row, even if it exists. Double-check your Prisma schema and your database schema to ensure the types match exactly. There are reports of issues when Prisma's introspection guesses the wrong type (e.g., decimal vs. integer) see this discussion. 2. Schema Drift: If your database schema and your Prisma schema are out of sync (for example, after manual changes in the DB), Prisma may not operate as expected. Try running prisma db pull to re-introspect and update your schema. 3. Relation Definition: Ensure your Prisma schema defines the relation correctly, e.g.:

   model foo {
     foo_uuid Int @id
     bars     bar[]
   }

   model bar {
     id           Int @id @default(autoincrement())
     foo_uuid     Int
     foo          foo @relation(fields: [foo_uuid], references: [foo_uuid])
     other_bar_field String
   }

   model foo {
     foo_uuid Int @id
     bars     bar[]
   }

   model bar {
     id           Int @id @default(autoincrement())
     foo_uuid     Int
     foo          foo @relation(fields: [foo_uuid], references: [foo_uuid])
     other_bar_field String
   }

If the relation is not defined properly, Prisma may not handle the foreign key as expected. 4. Unchecked Create Input: If you are using the "unchecked" create input, you must provide all foreign keys directly and cannot use connect or other relation helpers see this issue. 5. Case Sensitivity or Quoting: Sometimes, table or column names' case sensitivity or quoting can cause mismatches, especially if the database was created manually. 6. Prisma Client Version: Ensure your Prisma Client is up to date, as some bugs related to foreign key handling have been fixed in recent versions. If all of the above are correct and you still get the error, try to reproduce the issue in a minimal setup, as sometimes the problem is with the environment or schema drift. If you continue to have issues, please provide your Prisma schema and the relevant table definitions for further help. References: - Foreign key constraint failed on the field - Can't create a record with only the foreign key to another table - Optional relation using a required field makes model unusable 🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.

josephjnkOP•3mo ago

I ran prisma db pull followed by prisma generate; it did not fix the issue. I am using prisma client version 6.8.2 On further inspection I think there is some sort of race condition in my code; when I run things in a debugger the code works The problem was that I wasn't using Jest's --runInBand with my e2e tests, so the DB was getting clobbered.

Nurul•3mo ago

Thanks for letting us know. This should be helpful to someone else who might run into a simlar issue 👍

Gaming

Programming

Getting foreign key error even though foreign row exists

Did you find this page helpful?