SQL Server 和 MongoDB 中引用操作的特殊规则

如果你使用引用操作，某些数据库有特定的要求，你应该考虑这些要求。

¥Some databases have specific requirements that you should consider if you are using referential actions.

• 如果关系链导致循环或多个级联路径，则 Microsoft SQL Server 不允许对外键进行级联引用操作。如果外键上的引用操作设置为 NO ACTION 以外的值（如果 Prisma ORM 管理引用完整性，则为 NoAction），服务器将检查循环或多级联路径，并在执行 SQL 时返回错误。

  ¥Microsoft SQL Server doesn't allow cascading referential actions on a foreign key, if the relation chain causes a cycle or multiple cascade paths. If the referential actions on the foreign key are set to something other than NO ACTION (or NoAction if Prisma ORM is managing referential integrity), the server will check for cycles or multiple cascade paths and return an error when executing the SQL.

• 对于 MongoDB，在 Prisma ORM 中使用引用操作要求对于任何具有自引用关系或三个模型之间循环的数据模型，必须设置 NoAction 的引用操作，以防止引用操作模拟无限循环。请注意，默认情况下，MongoDB 使用 relationMode = "prisma" 模式，这意味着 Prisma ORM 管理 参照完整性。

  ¥With MongoDB, using referential actions in Prisma ORM requires that for any data model with self-referential relations or cycles between three models, you must set the referential action of NoAction to prevent the referential action emulations from looping infinitely. Be aware that by default, the relationMode = "prisma" mode is used for MongoDB, which means that Prisma ORM manages referential integrity.

给定 SQL：

¥Given the SQL:

CREATE TABLE [dbo].[Employee] (
  [id] INT NOT NULL IDENTITY(1,1),
  [managerId] INT,
  CONSTRAINT [PK__Employee__id] PRIMARY KEY ([id])
);

ALTER TABLE [dbo].[Employee]
  ADD CONSTRAINT [FK__Employee__managerId]
    FOREIGN KEY ([managerId]) REFERENCES [dbo].[Employee]([id])
    ON DELETE CASCADE ON UPDATE CASCADE;

当 SQL 运行时，数据库会抛出以下错误：

¥When the SQL is run, the database would throw the following error:

Introducing FOREIGN KEY constraint 'FK__Employee__managerId' on table 'Employee' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

在更复杂的数据模型中，查找级联路径可能会变得复杂。因此，在 Prisma ORM 中，在生成要在任何迁移期间运行的任何 SQL 之前验证数据模型，高亮作为路径一部分的关系。这使得找到并打破这些行动链变得更加容易。

¥In more complicated data models, finding the cascade paths can get complex. Therefore in Prisma ORM, the data model is validated before generating any SQL to be run during any migrations, highlighting relations that are part of the paths. This makes it much easier to find and break these action chains.

自关系（SQL Server 和 MongoDB）

¥Self-relation (SQL Server and MongoDB)

以下模型描述了一种自关系，其中 Employee 可以有一个管理者和被管理者，引用同一模型的条目。

¥The following model describes a self-relation where an Employee can have a manager and managees, referencing entries of the same model.

model Employee {
  id        Int        @id @default(autoincrement())
  manager   Employee?  @relation(name: "management", fields: [managerId], references: [id])
  managees  Employee[] @relation(name: "management")
  managerId Int?
}

这将导致以下错误：

¥This will result in the following error:

Error parsing attribute "@relation": A self-relation must have `onDelete` and `onUpdate` referential actions set to `NoAction` in one of the @relation attributes. (Implicit default `onDelete`: `SetNull`, and `onUpdate`: `Cascade`)

通过不定义任何操作，Prisma ORM 将使用以下默认值，具体取决于底层 标量场 是否设置为可选或必需。

¥By not defining any actions, Prisma ORM will use the following default values depending if the underlying scalar fields are set to be optional or required.

条款	所有标量字段都是可选的	至少需要一个标量场
onDelete	SetNull	NoAction
onUpdate	Cascade	Cascade

由于上述关系中 onUpdate 的默认引用操作是 Cascade，而 onDelete 的默认引用操作是 SetNull，因此会产生循环，解决方案是将 onUpdate 和 onDelete 值显式设置为 NoAction。

¥Since the default referential action for onUpdate in the above relation would be Cascade and for onDelete it would be SetNull, it creates a cycle and the solution is to explicitly set the onUpdate and onDelete values to NoAction.

model Employee {
  id        Int        @id @default(autoincrement())
  manager   Employee   @relation(name: "management", fields: [managerId], references: [id])
  manager   Employee   @relation(name: "management", fields: [managerId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  managees  Employee[] @relation(name: "management")
  managerId Int
}

三个表之间的循环关系（SQL Server 和 MongoDB）

¥Cyclic relation between three tables (SQL Server and MongoDB)

以下模型描述了 Chicken、Egg 和 Fox 之间的循环关系，其中每个模型引用另一个模型。

¥The following models describe a cyclic relation between a Chicken, an Egg and a Fox, where each model references the other.

model Chicken {
  id        Int   @id @default(autoincrement())
  egg       Egg   @relation(fields: [eggId], references: [id])
  eggId     Int
  predators Fox[]
}

model Egg {
  id         Int       @id @default(autoincrement())
  predator   Fox       @relation(fields: [predatorId], references: [id])
  predatorId Int
  parents    Chicken[]
}

model Fox {
  id        Int     @id @default(autoincrement())
  meal      Chicken @relation(fields: [mealId], references: [id])
  mealId    Int
  foodStore Egg[]
}

这将导致循环中的每个关系字段出现三个验证错误。

¥This will result in three validation errors in every relation field that is part of the cycle.

第一个是 Chicken 模型中的关系 egg：

¥The first one is in the relation egg in the Chicken model:

Error parsing attribute "@relation": Reference causes a cycle. One of the @relation attributes in this cycle must have `onDelete` and `onUpdate` referential actions set to `NoAction`. Cycle path: Chicken.egg → Egg.predator → Fox.meal. (Implicit default `onUpdate`: `Cascade`)

第二个是 Egg 模型中的关系 predator：

¥The second one is in the relation predator in the Egg model:

Error parsing attribute "@relation": Reference causes a cycle. One of the @relation attributes in this cycle must have `onDelete` and `onUpdate` referential actions set to `NoAction`. Cycle path: Egg.predator → Fox.meal → Chicken.egg. (Implicit default `onUpdate`: `Cascade`)

第三个是 Fox 模型中的关系 meal：

¥And the third one is in the relation meal in the Fox model:

Error parsing attribute "@relation": Reference causes a cycle. One of the @relation attributes in this cycle must have `onDelete` and `onUpdate` referential actions set to `NoAction`. Cycle path: Fox.meal → Chicken.egg → Egg.predator. (Implicit default `onUpdate`: `Cascade`)

由于需要关系字段，所以 onDelete 的默认引用动作是 NoAction，但 onUpdate 的默认引用动作是 Cascade，这会导致引用动作循环。解决方案是将任一关系中的 onUpdate 值设置为 NoAction。

¥As the relation fields are required, the default referential action for onDelete is NoAction but for onUpdate it is Cascade, which causes a referential action cycle. The solution is to set the onUpdate value to NoAction in any one of the relations.

model Chicken {
  id        Int   @id @default(autoincrement())
  egg       Egg   @relation(fields: [eggId], references: [id])
  egg       Egg   @relation(fields: [eggId], references: [id], onUpdate: NoAction)
  eggId     Int
  predators Fox[]
}

or

model Egg {
  id         Int       @id @default(autoincrement())
  predator   Fox       @relation(fields: [predatorId], references: [id])
  predator   Fox       @relation(fields: [predatorId], references: [id], onUpdate: NoAction)
  predatorId Int
  parents    Chicken[]
}

or

model Fox {
  id        Int     @id @default(autoincrement())
  meal      Chicken @relation(fields: [mealId], references: [id])
  meal      Chicken @relation(fields: [mealId], references: [id], onUpdate: NoAction)
  mealId    Int
  foodStore Egg[]
}

两个模型之间的多个级联路径（仅限 SQL Server）

¥Multiple cascade paths between two models (SQL Server only)

数据模型描述了相同模型之间的两条不同路径，这两种关系都会触发级联引用操作。

¥The data model describes two different paths between same models, with both relations triggering cascading referential actions.

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

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

model Comment {
  id          Int  @id @default(autoincrement())
  writtenById Int
  postId      Int
  writtenBy   User @relation(fields: [writtenById], references: [id])
  post        Post @relation(fields: [postId], references: [id])
}

该数据模型中的问题是如何存在从 Comment 到 User 的两条路径，以及两个关系中的默认 onUpdate 操作如何是 Cascade。这会导致两个验证错误：

¥The problem in this data model is how there are two paths from Comment to the User, and how the default onUpdate action in both relations is Cascade. This leads into two validation errors:

第一个是关系 writtenBy：

¥The first one is in the relation writtenBy:

Error parsing attribute "@relation": When any of the records in model `User` is updated or deleted, the referential actions on the relations cascade to model `Comment` through multiple paths. Please break one of these paths by setting the `onUpdate` and `onDelete` to `NoAction`. (Implicit default `onUpdate`: `Cascade`)

第二个是关系 post：

¥The second one is in the relation post:

Error parsing attribute "@relation": When any of the records in model `User` is updated or deleted, the referential actions on the relations cascade to model `Comment` through multiple paths. Please break one of these paths by setting the `onUpdate` and `onDelete` to `NoAction`. (Implicit default `onUpdate`: `Cascade`)

该错误意味着，通过更新 User 模型中的记录中的主键，更新将通过 writtenBy 关系在 Comment 和 User 之间级联一次，并且由于 Post 与 Comment 模型相关，因此会再次从 post 关系通过 Post 模型级联。

¥The error means that by updating a primary key in a record in the User model, the update will cascade once between the Comment and User through the writtenBy relation, and again through the Post model from the post relation due to Post being related with the Comment model.

修复方法是将 writtenBy 或 post 关系字段中的 onUpdate 引用操作设置为 NoAction，或者通过更改 author 关系中的操作从 Post 模型设置：

¥The fix is to set the onUpdate referential action to NoAction in the writtenBy or post relation fields, or from the Post model by changing the actions in the author relation:

model Comment {
  id          Int  @id @default(autoincrement())
  writtenById Int
  postId      Int
  writtenBy   User @relation(fields: [writtenById], references: [id])
  writtenBy   User @relation(fields: [writtenById], references: [id], onUpdate: NoAction)
  post        Post @relation(fields: [postId], references: [id])
}

or

model Comment {
  id          Int  @id @default(autoincrement())
  writtenById Int
  postId      Int
  writtenBy   User @relation(fields: [writtenById], references: [id])
  post        Post @relation(fields: [postId], references: [id])
  post        Post @relation(fields: [postId], references: [id], onUpdate: NoAction)
}

or

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