Invalid `prisma.mytable.create()` invocation: Foreign key constraint failed on the field: `(not available)` when using cockroachdb

Question

I'm attempting to use Prisma with Cockroachdb locally. I understand that official support for CockroachDB is in the works.

I have a parallel local PostgreSQL database where everything is working correctly and I am able to generate Prisma migrations to run manually against Cockroachdb. All of this works and I end up with two apparently identical schemas.

However, any create operation in Prisma using the connect feature is failing with the following error:

Invalid `prisma.mylinkedtable.create()` invocation:
  Foreign key constraint failed on the field: `(not available)`

Here are key parts of my schema.prisma:

datasource db {
  provider = "postgresql"
  url      = "postgresql://user:pass@localhost:26257/mydb"
}

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["cockroachdb"]
}

model MyLinkedEntity {
  id           Int      @id @default(autoincrement())
  myEntity     MyEntity  @relation(fields: [myEntityId], references: [id])
  myEntityId    Int
  // ...
}

model MyEntity {
  id             Int           @id @default(autoincrement())
  // ...
}

The code that is triggering the foreign key constraint:

const entity = await prisma.myEntity.findFirst({})
    
await prisma.myLinkedEntity.create({
    data: {
        myEntityId: entity.id,
        // ...
    }
}

If I go about it slightly diffently and try to link using the connect feature:

await prisma.myLinkedEntity.create({
    data: {
        myEntity: {
            connect: {
                id: entity.id
            }
        },
        // ...
    }
}

I get a different error:

Invalid `prisma.myLinkedEntity.create()` invocation:


  An operation failed because it depends on one or more records that were required but not found. No 'MyEntity' record(s) (needed to inline the relation on 'MyLinkedEntity' record(s)) was found for a nested connect on one-to-many relation 'MyEntityToMyLinkedEntity'.

What gives?

score 4 · Answer 1 · answered Dec 07 '21 at 15:23

It looks like the related rows are not being created first.

Could you try using connectOrCreate instead? https://www.prisma.io/docs/concepts/components/prisma-client/relation-queries#connect-or-create-a-record

It could also be something wrong with the Prisma model. I'm not not sure if order matters, but I notice you have the relation defined before the ID it's referencing.

One final, unrelated, point: you should not use auto incrementing ints as keys in CockroachDB. See https://www.cockroachlabs.com/docs/v21.2/schema-design-table#primary-key-best-practices

Invalid `prisma.mytable.create()` invocation: Foreign key constraint failed on the field: `(not available)` when using cockroachdb

1 Answers1