errno: 150 "Foreign key constraint is incorrectly formed"

[Ahmadh26]

I'm adding a foreign key to a table as follows:

model Sales {
  id            String        @id
  nsid          String
  user          User          @relation(fields: [nsid], references: [nsid], onDelete: Cascade)
  product_id    String
  product       Products      @relation(fields: [product_id], references: [id], onDelete: Cascade)
  creation_time DateTime      @default(now())
  purchase_type purchase_type
  support_type  support_type
  valid_until   DateTime
  purchase_code String
  author_id     String
  author        Author        @relation(fields: [author_id], references: [id], onDelete: Cascade)
  url           String        @db.Text
  notify_email  Int           @default(1)
  currency_id   Int           @default(1)
  author_fee_id Int
  discount      String
  receipt_id    String
 //                                !!!!                 ERROR IS IN THE NEXT LINE                 !!!
  transaction   Transactions  @relation(fields: [receipt_id], references: [receipt_id], onDelete: Cascade)
  ratings       Ratings[]
}

model Transactions {
  id                 String   @id
  amount             String
  currency           String
  gateway            String
  payment            String
  status             String
  created            String
  receipt_id         String
  nsid               String
  user               User     @relation(fields: [nsid], references: [nsid], onDelete: Cascade)
  last_four          String
  brand              String
  cus_id         String?  @db.Text
  response_code      String?
  response_message   String?
  creation_time      DateTime @default(now())
  sales            Sales[]
}

when saving and generating the schema, and pushing or migrating the new schema, i get the following error:

Error: Can't create table `test`.`sales` (errno: 150 "Foreign key constraint is incorrectly formed")
   0: sql_migration_connector::apply_migration::apply_migration

I follow the error to the following line:

-- AddForeignKey
ALTER TABLE `Sales` ADD CONSTRAINT `Sales_receipt_id_fkey` FOREIGN KEY (`receipt_id`) REFERENCES `Transactions`(`receipt_id`) ON DELETE CASCADE ON UPDATE CASCADE;

How is it not working? can someone guide me on what am i doing wrong?

[austincrim]

Hey @Ahmadh26 👋 ,

Could you try adding a @unique attribute to the Transaction.receipt_id field in your schema? One-to-one relations require a unique or ID constraint.

model Transactions {
  id                 String   @id
  ...
  receipt_id         String   @unique  // <--------
  ...
}

[Ahmadh26]

I will do so as soon as I'm back on my PC.

One question though, this is a one to many relation between transactions and sales respectively, is the @unique attribute still required on the transaction column if so?

Thanks for your help!

[austincrim]

Oops, I misread your schema, it is one-to-many. It is still required for @relation to reference a unique field, though.

[Ahmadh26]

Thanks, solved my problem! :)

[ciscoheat]

This is not related to the exact question, but I got the same error 150 and it turned out that using the exact same type on both sides is important. If a key is @db.TinyInt, the foreign key cannot be just Int for example, it has to match.

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that have already been marked as answered but remain open.

If this discussion still requires further input or clarification, feel free to reopen it or start a new one with updated details. Your contributions are invaluable to the community, and we’re here to help!

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and ongoing support of the Prisma community!