Hm if I have ModelA that has “Many” ModelB, and I create a ModelA entry with a few ModelB rows in the database, when I delete ModelA using a manual query, it cascade deletes all ModelB as well, as expected, due to prisma automatically adding the cascade behaviour to the table definition. (on update cascade on delete cascade) The behaviour also works as expected using queryRaw and deleting the parent row by ID. However when I try to use the generated prisma client to delete ModelA by ID, I get an error like this: invalid prisma.ModelA.delete()` invocation:

The change you are trying to make would violate the required relation 'ModelAtoModelB' between the `ModelA` and `ModelB` models.

In the generated client, I can see that relationship has: “relationOnDelete”:“NONE”, but the table has on delete cascade as generated by the schema.prisma definitions. So how does one define this relationship so that when an owning side of an relationship is deleted, the children are deleted as well?

Hey @MichaelHindley 👋 The reason that the raw query is working and the Prisma query isn’t is because of this issue here. Unfortunately Prisma doesn’t support specifying cascade behaviour right now due to this request being open, we will be adding this feature soon after Migrate is stable. As a workaround, you can use this tool made by a community member to specify cascade behaviour on relations 🙂

oh awesome, that explains it 🙂 tyvm!

@MichaelHindley The nullable side is always where the relation scalar is defined and not on the many side which is virtual. For e.g. if I have a schema like this:

model User {
  id        Int      @id @default(autoincrement())
  name      String
  posts     Post[]
}

model Post {
  id        Int      @id @default(autoincrement())
  published Boolean  @default(false)
  title     String
  content   String?
  authorId  Int?
  author    User?    @relation(fields: [authorId], references: [id])
}

I can define the 1-many relationship to be nullable by setting the

authorId

as optional. This means that it’s an optional relationship. This can only be done where the actual scalar (

authorId

) is defined and not the many side.

So take this example then, where I’ve made the relationship optional, even though it actually shouldn’t be according to the domain. Given this schema, I can’t delete SupplyChain by ID due to the same error at the start of this thread, making the relationship optional on the virtual side doesn’t change this behaviour. Given your example, the User can not be deleted by ID either if I understand correctly then?

In this case, if you delete the

SupplyChain

then the

SupplyChainTransportStep

connected to it will also be deleted if

on delete cascade

has been set. In my example, the User can be successfully deleted by

id

and the corresponding posts will be deleted as well as I have specified

on delete cascade

because the relationship is optional.

Interesting, I get the mentioned error in the above case, although before this conversation, the many side was not optional, adding it and migrating didn’t change the prisma client behaviour when deleting. The resulting migration was

ALTER TABLE "SupplyChainTransportStep" ALTER COLUMN "supplyChainId" DROP NOT NULL

so it feels like

on delete cascade

should also be added there from the

SupplyChain

side? Ihe

SupplyChainTransportStep

table has this defined since before though:

"supplyChainId"   text
    constraint "SupplyChainTransportStep_supplyChainId_fkey"
        references "SupplyChain"
        on update cascade on delete cascade

Ohh. I have tried this on a completely new setup without any previous migrations. Let me try it with an already present

not null

constraint and check after dropping it. Also, if your

SupplyChainTransportStep

has

on delete cascade

then the following should work on optional relations.

I mean yeah it works perfectly fine as far as the database is concerned, the error is triggered by the prisma client and it’s disallowing the operation, like mentioned, if the query is manually executed with queryRaw or db console, everything works as expected

It works fine with

prisma.user.delete

as well. Here’s the schema I tried on:

model User {
  id    Int     @id @default(autoincrement())
  name  String?
  posts Post[]
}

model Post {
  id     Int    @id @default(autoincrement())
  title  String
  user   User  @relation(fields: [userId], references: [id])
  userId Int
}

At first, the relation is not null. I added random users with their posts. When I try to delete the user, I get the error what you’re facing above. Next I make the relation optional by adding a

?

and running migrate again:

model User {
  id    Int     @id @default(autoincrement())
  name  String?
  posts Post[]
}

model Post {
  id     Int    @id @default(autoincrement())
  title  String
  user   User?  @relation(fields: [userId], references: [id])
  userId Int?
}

This time on running

prisma.user.delete

it works perfectly fine.

Sorry for lack of messages, haven’t had time to sit with code since but I will test and report back as soon as I have!

Ohh. I guess it might be something in the Migrations then 🤔

for what it’s worth, there is no difference at all in the shape of the database between using migrations vs db push

But

db push

is actually meant to be used in development only as a prototyping mechanism 🙂

@MichaelHindley In general there should be no difference in the end-state of the database schema when using db push vs migrate. However, there might be cases in which db push might not able to perform the changes and there is absolutely no control over how the actual schema changes are carried out, as opposed to when using Prisma Migrate where you can review the migration script without applying it. So while it might be convenient to use db push, I would not advise using it with production databases.

thanks for the heads up, yeah we’re not intending to use it that way, this was seen as a quick escape hatch from finding the root cause of why the prisma client refused to fire a valid and supported delete query