Referential integrity

Referential integrity is a property of a data set that states that all its references are valid. Referential integrity requires that if one record references another, then the referenced record must exist. For example, if a Post model defines an author, then the author must also exist. Referential integrity is enforced through constraints that prevent you making changes that break references, and through defining referential actions that run when updating or deleting records. In the case of the Post model, for example, you must define what happens if its author is deleted.

In Prisma, referential integrity is implemented by defining relations between records using the @relation attribute. The onUpdate and onDelete arguments of this attribute can be used to define which referential action take place on updating or deleting the related record.

For example, take the following blog post schema, with a one-to-many relation between Posts and Users:

schema.prisma
1model Post {
2 id Int @id @default(autoincrement())
3 title String
4 author User @relation(fields: [authorId], references: [id], onDelete: Cascade)
5 authorId Int
6}
7
8model User {
9 id Int @id @default(autoincrement())
10 posts Post[]
11}

In this case, the onDelete: Cascade argument specifies the Cascade referential action, deleting the Post if the related User is deleted. See Referential actions for further details of supported actions.

When using a relational database that supports foreign keys, referential integrity can be handled in the underlying database. This is the default option in Prisma when using SQL databases.

Alternatively, Prisma can emulate referential integrity in the client. This is the default option when using MongoDB as your database, because it does not support foreign keys. You can also enable emulation for relational databases in cases where foreign keys are not supported, such as when using PlanetScale, or where it is preferred for other reasons (for example, in cases where there are scaling concerns).

There are significant performance implications to emulating referential integrity in the Prisma client. In cases where the underlying database can handle referential integrity with foreign keys, it is usually the preferred choice.

Setting the referential integrity in the datasource

Referential integrity is currently a Preview feature. To enable this, add it to the previewFeatures list in the generator block of schema.prisma:

schema.prisma
1datasource db {
2 provider = "mysql"
3 url = env("DATABASE_URL")
4 referentialIntegrity = "prisma"
5}
6
7generator js {
8 provider = "prisma-client-js"
9 previewFeatures = ["referentialIntegrity"]
10}

To change the type of referential integrity used, update the referentialIntegrity parameter in the datasource block. The available options are:

  • foreignKeys: this handles referential integrity in the database using foreign keys. This is the default option when using all SQL database connectors.
  • prisma: this emulates referential integrity in the Prisma client. This is the default (and only available) option when using the MongoDB connector. You should also enable this option when using the MySQL connector with a PlanetScale database.
When using Prisma Migrate, setting the value to `prisma` will remove all previously created foreign keys in the next migration. Setting the value to `foreignKeys` will create foreign keys for all relations in the next migration.

Handling the referential integrity in Prisma

In the case where referential integrity is emulated in the Prisma client using the prisma option for the referentialIntegrity parameter, there is currently a reduced number of supported referential actions available in the data model:

Referential actiononUpdateonDelete
Restrictnoyes
SetNullnoyes
SetDefaultnono
Cascadenoyes
NoActionnoyes

Currently Prisma only implements the referential actions. Foreign keys also create constraints, which make it impossible to manipulate data in a way that would violate these constraints: instead of executing the query, the database responds with an error. These constraints will not be created if you emulate referential integrity in the client, so if you set the referential action to NoAction there will be no checks to prevent you from breaking the referential integrity.

Another current limitation when emulating referential integrity in Prisma is that any raw database queries will not trigger any of the actions, and they should be handled manually if deleting or updating records using the raw queries.

When using the db pull command, existing relations are kept in the data model. This has some caveats:

  • If you delete a model that is part of a relation, Prisma will delete the relation.

  • If you delete or rename a scalar field used in a relation field, the relation field will still refer to the old version, leading to validation errors. For example, take the following data model:

    schema.prisma
    1model A {
    2 field Int
    3 b B @relation(fields: [field], references: [id])
    4}

    If you then change field to renamedField, you will end up with the following data model after then next db pull:

    schema.prisma
    1model A {
    2 renamedField Int
    3 b B @relation(fields: [field], references: [id])
    4}

    In this case, you need to manually fix the relation to point to renamedField instead before continuing to use the data model.

Handling the referential integrity with foreign keys

In the case where referential integrity is handled in the database using the foreignKeys option for the referentialIntegrity parameter, all referential actions supported by the database are available:

actiononUpdateonDelete
Restrict⟒yesyes
SetNullyesyes
SetDefault⟑yesyes
Cascadeyesyes
NoActionyesyes
  • ⟒ Not supported on SQL Server
  • ⟑ Not supported on MySQL/InnoDB

In this mode the raw database queries will trigger actions accordingly.

Edit this page on GitHub