prisma: Error: Database error: Foreign key constraint failed

Bug description

I have the following schema:

model User {
  id                String @id @default(uuid())
  ...
  address           Address? @relation(fields: [id], references:[entityId])
}

model Address {
  entityId    String @id
  city        String?
  line1       String?
  line2       String?
  postalCode  String?
  user        User?
}

I execute: npx prisma db push

I am using: Postgres

The error I get:

Error: Database error
Foreign key constraint failed: User_id_fkey
   0: sql_migration_connector::sql_database_step_applier::apply_migration
             at migration-engine/connectors/sql-migration-connector/src/sql_database_step_applier.rs:15
   1: migration_core::api::SchemaPush
             at migration-engine/core/src/api.rs:163

Am I missing something conceptually? The data model seems valid to me. I use the VS Code plugin to lint my schema and it’s not showing any errors.

How to reproduce

As seen above.

Expected behavior

The new data model to be pushed correctly to the database.

Prisma information

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

Version: "prisma": "^2.22.1",

Environment & setup

  • OS: MacOS
  • Database: PostgreSQL
  • Node.js version: 12.14.0
  • Prisma version: 2.22.1
prisma               : 2.22.1
@prisma/client       : 2.21.2
Current platform     : darwin
Query Engine         : query-engine 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 60cc71d884972ab4e897f0277c4b84383dddaf6c (at node_modules/@prisma/engines/prisma-fmt-darwin)
Default Engines Hash : 60cc71d884972ab4e897f0277c4b84383dddaf6c
Studio               : 0.379.0

About this issue

  • Original URL
  • State: closed
  • Created 3 years ago
  • Reactions: 21
  • Comments: 22 (10 by maintainers)

Most upvoted comments

For those of you that have made it here and haven’t found a solution, my problem was with how my Prisma query was written. Instead of plainly adding the foreign key’s identifier in the data field (A), I changed to connect (B).

For some reason, my development environment was allowing me to successfully create an entry with the first implementation. It wasn’t until it was deployed that it would error out. Same Prisma version, same database. 🤷🏻‍♂️

Schema:

model user {
  userId     String    @id @default(cuid())
  hospitalId String
  firstName String
  hospital   hospital? @relation(fields: [hospitalId], references: [hospitalId])
}

model hospital {
  hospitalId String @id @default(cuid())
  users      user[]
}

(A) Inlined foreign identifier: Worked in local dev, failed in production

  const user = await prisma.user.create({
    data: {
      firstName,
      hospitalId
    }
  });

(B) Connect records: Worked in both local dev and in production

  const user = await prisma.user.create({
    data: {
      firstName,
      hospital: {
        connect: {
          hospitalId
        }
      }
    }
  });

It happened with me. I was getting the same error when creating objects inside objects in a relation from one to many.

The issue has been causd by the hierarchaly minor object beeing created before the higher one.

The solution was to seed manually object by object.

Other cause to the error was the object being created with the id bond and the “father” had a different id.

I’ve used prisma.objectname.create with id inclused. One by one.

Hope it helped.

Obs.: I thought about make an logical sequence that allows the subsquent seed to happen only after previous. Didn’t implement anything like that. If somebody implemented this solution or have a better one, please let me know.

Obrigado. =) lucas_araujo_11@hotmail.com

If there is someone still facing this issue, Before proceeding with the migration, you can manually drop the existing foreign key constraint in your database. However, be cautious when doing this, as it can affect data integrity. Run the following SQL command to drop the constraint:

ALTER TABLE <ChildTable> DROP FOREIGN KEY <ForeignKeyName> ; `

Replace <ChildTable> with the name of the table containing the constraint you wish to remove and <ForeignKeyName> with the actual name of the foreign key constraint that needs to be dropped

Can you quickly split this out into a standalone issue @orta? Seems we are missing a validation here.