SQL Server: When a referenced record is deleted or modified, an error is raised. See the SQLite docs for more information. SQLite: When a related primary key is modified or deleted, no action is taken. MySQL: NoAction behaves exactly the same as Restrict. See the PostgreSQL docs for more information. PostgreSQL: NoAction allows the check (if a referenced row on the table exists) to be deferred until later in the transaction. The NoAction action is similar to Restrict, the difference between the two is dependent on the database being used: Instead, you can use NoAction, which produces the same result and is compatible with SQL Server. The Restrict action is not available on Microsoft SQL Server and triggers a schema validation error. onUpdate: Cascade Updates the relation scalar fields if the referenced scalar fields of the dependent record are updated. onDelete: Cascade Deleting a referenced record will trigger the deletion of referencing record. Restrict is not available for SQL Server databases, but you can use NoAction instead. For all other database providers, Prisma rejects the schema with a validation error. However, this raises a foreign key constraint error when the action is triggered at runtime.įor this reason, when you set postgres as the database provider in the (default) foreignKeys relation mode, Prisma warns users to mark as optional any fields that are included in a attribute with a SetNull referential action. PostgreSQL is the only database supported by Prisma that allows you to define a SetNull referential action that refers to a non-nullable field. In MySQL versions 5.6 and later, and MariaDB versions before 10.5, attempting to create a table definition with the SET DEFAULT referential action fails with a syntax error.įor this reason, when you set mysql as the database provider, Prisma warns users to replace SetDefault referential actions in the Prisma schema with another action. You can define tables using the SET DEFAULT referential action, but a foreign key constraint error is triggered at runtime. In MySQL versions 8 and later, and MariaDB versions 10.5 and later, SetDefault effectively acts as an alias for NoAction. The exact behavior depends on the database version: MySQL, and the underlying InnoDB storage engine, does not support SetDefault. However, there are special cases where some relational databases diverge from the standard. Referential actions are part of the ANSI SQL standard. †† Referential actions for MongoDB are available in Prisma versions 3.7.0 and later. The following model defines a one-to-many relation between User and Post and a many-to-many relation between Post and Tag, with explicitly defined referential actions: 
If you do not specify a referential action, Prisma falls back to a default.
Referential actions are defined in the attribute and map to the actions on the foreign key constraint in the underlying database. In your Prisma schema, this is generally represented by the id field on the related model.īy default a database will reject any operation that violates the referential integrity, for example, by deleting referenced records. Referential integrity states that these foreign keys must reference an existing primary key value in the related database table. These foreign keys connect the models on the database level. When you define relationships between data models in your Prisma schema, you use relation fields, which do not exist on the database, and scalar fields, which do exist on the database. Referential actions are features of foreign key constraints that exist to preserve referential integrity in your database.
0 kommentar(er)
