Foreign key constraint failed on the field: foreign key

[kivox]

Hi!

I'm trying to delete a "project" row but get a warning for Foreign key constraints. This only happened after I added the member model as I was previously connecting users directly to the project model, this is because I'd like to add a permission system and other things on a per user basis.

schema.prisma

model User {
  id        Int       @id @unique @default(autoincrement())
  email     String    @unique
  projects  Member[]  @relation("user_projects")
  owner     Project[] @relation("project_owner")
  invites   Invite[]  @relation("invitee")
}

model Project {
  id        Int      @id @unique @default(autoincrement())
  name      String   @default("Unnamed Project")
  members   Member[] @relation("project_members")
  invites   Invite[] @relation("project_invites")
  apps      App[]    @relation("project_apps")
  owner     User     @relation("project_owner", fields: [ownerID], references: [id])
  ownerID   Int
}

model Invite {
  id        Int      @id @unique @default(autoincrement())
  invitee   User     @relation("invitee", fields: [inviteeID], references: [id])
  inviteeID Int
  project   Project  @relation("project_invites", fields: [projectID], references: [id])
  projectID Int
  email     String
}

model Member {
  id        Int      @id @unique @default(autoincrement())
  project   Project  @relation("project_members", fields: [projectID], references: [id])
  projectID Int
  user      User     @relation("user_projects", fields: [userID], references: [id])
  userID    Int
}

model App {
  id        Int      @id @unique @default(autoincrement())
  name      String
  project   Project  @relation("project_apps", fields: [projectID], references: [id])
  projectID Int
}

This is how I query it:

await prisma.project.delete({
	where: {
		id: parseInt(req.params.project)
	},
})

output:

DELETE: /projects/1

PrismaClientKnownRequestError: 
Invalid `prisma.project.delete()` invocation in
/home/src/api/routes/projects/delete.js:6:46

  3 
  4 module.exports = async (req, res) => {
  5     try {
→ 6             const project = await prisma.project.delete(
  Foreign key constraint failed on the field: `foreign key`
    at cb (/home/src/api/node_modules/@prisma/client/runtime/index.js:38683:17)
    at async module.exports (/home/src/api/routes/projects/delete.js:6:19) {
  code: 'P2003',
  clientVersion: '3.6.0',
  meta: { field_name: 'foreign key' }
}

[janpio]

This might help: https://www.prisma.io/docs/concepts/components/prisma-schema/relations/referential-actions

[davidyell]

I implemented this approach, but still ended up here because of the same issue.

(Apologies for the naming, as I am learning Prisma still 🎓 )

I am using SQLite as my data store, which is supported

Although my configuration differs slightly, as I am trying to delete a One to Many. My expectation is that deleting a Decks would cascade delete all the related CardsInDecks records.

model CardsInDecks {
  id           Int     @id @default(autoincrement())
  deck_id      Int
  card_id      Int
  quantity     Int     @default(1)
  is_sideboard Boolean @default(false)
  decks        Decks   @relation(fields: [deck_id], references: [id], onDelete: Cascade)
  cards        Cards   @relation(fields: [card_id], references: [id], onDelete: NoAction, onUpdate: NoAction)

  @@index([deck_id, card_id], map: "cards_in_decks_deck_id_IDX")
  @@map("cards_in_decks")
}

model Decks {
  id             Int            @id @default(autoincrement())
  name           String
  player_id      Int
  created        DateTime       @default(now())
  updated        DateTime?      @updatedAt
  player         Players        @relation(fields: [player_id], references: [id], onDelete: NoAction, onUpdate: NoAction)
  cards_in_decks CardsInDecks[]

  @@index([player_id], map: "decks_player_id_IDX")
  @@map("decks")
}

I am calling it in the standard way,

const result = await prismaClient.decks.delete({
  where: { id: deckId },
});

..but I get the error mentioned by OP.

  152 const remove = async (request: Request, response: Response) => {
  153   const deckId: Decks['id'] = parseInt(request.params.id, 10);
  154
→ 155   const result = await prismaClient.decks.delete(
  Foreign key constraint failed on the field: `foreign key`

[davidyell]

Apologies, I solved this soon after I wrote this.

The key point I was missing was that I needed to push these schema changes into the database, whereas I was just generating a new client.

So be sure to yarn prisma db push

[Lexpeee]

I would like to add, I'm currently at a point where I'm still learning how to implement Prisma to my nestjs server project. Here is my schema:

model posts {
  id          String    @id @default(uuid()) @db.Uuid
  userId      String    @db.Uuid
  dateCreated DateTime  @default(now())
  dateRemoved DateTime?
  content     String
  embed       Json?
  attachments Json?
  likes       Int
  user        users     @relation(fields: [userId], references: [id])
}

model users {
  id          String    @id @default(uuid()) @db.Uuid
  username    String
  password    String
  status      String
  dateCreated DateTime  @default(now())
  dateRemoved DateTime?
  posts       posts[]
}

And the error I got:

const newPost = await this.prisma.posts.create(
Foreign key constraint failed on the field: `posts_userId_fkey (index)`

Pardon me for the lack of naming conventions and stuff. lol
I tried adding a new data to the database, this error occurs when the userID i put in doesn't exist in the user database. Just wanting to share my experience for the upcoming learniers. :)

[Abdul-Manan-456]

i have faced the same issue, but now solve. thanks..............