Special rules for referential actions in SQL Server

Microsoft SQL Server doesn't allow cascading referential actions on a foreign key, if the relation chain causes a cycle or multiple cascade paths. Most SQL databases handle these references out of the box.

If the referential actions on the foreign key are set to something other than NO ACTION, the server will check for cycles or multiple cascade paths and return an error when executing the 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;

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.

In more complicated data models, finding the cascade paths can get complex. Therefore in Prisma, 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.

Self-relation

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`)

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

ClauseAll of the scalar fields are optionalAt least one scalar field is required
onDeleteSetNullNoAction
onUpdateCascadeCascade

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)
managed Employee[] @relation(name: "management")
managerId Int
}

Cyclic relation between three tables

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.

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`)

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`)

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`)

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[]
}

Multiple cascade paths between two models

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])
}

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:

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`)

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`)

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.

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[]
}
Edit this page on GitHub