Cascade delete does not work unless relation is optional

[dodas]

Bug description

When I set db CONSTRAINT to CASCADE on delete and relation is marked as required in schema, prisma errors with

The change you are trying to make would violate the required relation 'UserToPost' between the `User` and `Post` models.

It works when I set the foreign key and the relation field as optional in prisma schema.

How to reproduce

1. Set delete behaviour to cascade in db:

ALTER TABLE public.Post
DROP CONSTRAINT Post_authorId_fkey,
ADD CONSTRAINT Post_authorId_fkey
   FOREIGN KEY (authorId)
   REFERENCES public.User (id)
   ON DELETE CASCADE
   ON UPDATE CASCADE;

2. create at least one User and one Post that belongs to him.
3. try to delete that User with prisma client

Expected behavior

The user and all his posts should be deleted.

Prisma information

schema:

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

model Post {
  id        Int   @id @default(autoincrement())
  author    User  @relation(fields: [authorId], references: [id])
  authorId  Int
}

Environment & setup

DB: Postgres 11.4
Prisma: 2.0.0-beta.2
Node: 12.2.0

[pantharshit00]

I can confirm this. We need to loosen the validation in the query engine

[pantharshit00]

SQL of the database for direct reproduction:

DROP TABLE IF EXISTS "public"."Post";

-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS "Post_id_seq";

-- Table Definition
CREATE TABLE "public"."Post" (
    "authorId" int4 NOT NULL,
    "id" int4 NOT NULL DEFAULT nextval('"Post_id_seq"'::regclass),
    PRIMARY KEY ("id")
);

DROP TABLE IF EXISTS "public"."User";
-- Sequence and defined type
CREATE SEQUENCE IF NOT EXISTS "User_id_seq";

-- Table Definition
CREATE TABLE "public"."User" (
    "id" int4 NOT NULL DEFAULT nextval('"User_id_seq"'::regclass),
    PRIMARY KEY ("id")
);

INSERT INTO "public"."Post" ("authorId", "id") VALUES
('1', '1');

INSERT INTO "public"."User" ("id") VALUES
('1');

ALTER TABLE "public"."Post" ADD FOREIGN KEY ("authorId") REFERENCES "public"."User"("id") ON DELETE CASCADE ON UPDATE CASCADE;

You can introspect that database and try a deletion query as shown below to reproduce this:

  const del = await prisma.user.delete({
    where: {
      id: 1,
    },
  });

[mavilein]

This is not a bug but by design. We enforce required relations on the Prisma layer. The schema clearly states that Post.author is required. The query engine finds a related Post record for the user and hence rejects the change.
I see two possible fixes:

1. You could change Post.author to be optional for now. (as the title suggests)
2. The real long term fix is to implement cascading deletes. Then the information about cascading deletes would be present in the schema and the query engine could act accordingly. This is tracked here.

[dodas]

Please don't say it's not a bug.
It is clearly documented that cascade delete should work if it is defined on the database level.

Changing all relations to optional would generate TS types as nullable and that means a lot of (previously unnecessary) null checking in TS.

Hopefully this can be fixed shortly, as it's quite annoying.

Thanks for great work on prisma!

[mavilein]

duplicate of: #2057

[pantharshit00]

Closing as a duplicate of #2057