Prisma-client-js: Delete record with relations (ConstraintViolation)

[Rutherther]

I came across a ConstraintViolation when deleting many to many relation:

ConnectorError(QueryError(SqliteFailure(Error { code: ConstraintViolation, extended_code: 787 }, Some("FOREIGN KEY constraint failed"))

i. e. I have entities linked with users, it creates table _EntityToUser.
When I delete an entity, it does not automatically delete records in EntityToUser and thus it fails.
I can disconnect the users using update, but I think it would be much more friendly if I could specify whether I want to disconnect users directly in delete

I also think, that it would be handy if photon could identify ConstraintViolations and would return the same error for all database engines

For SQLite I get this:

ConnectorError(QueryError(SqliteFailure(Error { code: ConstraintViolation, extended_code: 787 }, Some("FOREIGN KEY constraint failed"))

and for MySQL I get this:

ConnectorError(QueryError(MySqlError { ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db`.`_EntityToUser`, CONSTRAINT `_EntityToUser_ibfk_1` FOREIGN KEY (`A`) REFERENCES `Entity` (`id`)) }

Requests:

1. Identify ConstraintViolation errors and return the same error for every database engine
2. Allow to specify whether to disconnect relations in delete, so there is no need to update and delete afterwards

[timsuchanek]

Thanks @Rutherther. Can you share your datamodel or a subset including the relevant parts with us please?

[Rutherther]

Sure, I have made a repo for this: https://github.com/Rutherther/prisma2-many-to-many
Model:

model User {
  id    String  @default(cuid()) @id @unique
  email String  @unique
  name  String?
  entities Entity[]
}

model Entity {
  id      String  @default(cuid()) @id @unique
  users   User[]
  name    String
}

Code:

  const newEntity = await photon.entities.create({
    data: {
      name: 'Test entity',
      users: {
        connect: {
          email: '[email protected]'
        }
      }
    }
  });

  console.log(newEntity);

  await photon.entities.delete({
    where: {
      id: newEntity.id
    }
  }); // Fails

The problem is that I would have to update the entity (disconnect the users) before I delete it
I think it would be handy to add disconnect to delete, too. What do you think?

[JorgeCeja]

Any updates?
Running into a similar issue on preview5 when creating a one-to-one relationship
Thanks!

[Rutherther]

@JorgeCeja As a workaround you can first use update with disconnect

const entity; // ...
const users = await photon.entities.findOne({ where: { id: entity.id } }).users({ select: { id: true } });

await photon.entities.update({
  data: {
    users: {
      disconnect: users
    }
  }
});

await photon.entities.delete({ where: { id: entity.id  } });

This is an example for one-to-many relationship, but I think something like this should work for one-to-one

It would still be nice to have something like that in delete itself, too

[janpio]

Can you confirm the repro please @pantharshit00? Thanks.

[pantharshit00]

I am no longer able to reproduce this one.

I am on version [email protected], binary version: bbbeff6f84b408e534ebd866bfd378748e6d6611