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)
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:
(A) Inlined foreign identifier: Worked in local dev, failed in production
(B) Connect records: Worked in both local dev and in production
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.