[SQL Server] add validation for disallowed relationships (e.g. cyclic)

[mavilein]

By default we render relation fields as a foreign key with an ON UPDATE CASCADE clause. This can cause problems with SQL Server as certain kinds of relation setups are not possible. Running migrations for those kinds of schemas results in errors such as:

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

We must add validation to the parser for the following cases in order to show a nice error to the user.

Problem 1: Cyclic relationships

SQL Server does not allow to declare cyclic relationships such as the following.

model Album {
    id        Int      @id @default(autoincrement())
    artist_id Int
    artist    Artist   @relation(fields: [artist_id], references: [id])
    Artist    Artist[] @relation("FK_fav_album")
}

model Artist {
    id                Int     @id @default(autoincrement())
    favorite_album_id Int
    // this relation field makes it cyclic
    favorite_album    Album   @relation(fields: [favorite_album_id], references: [id], name: "FK_fav_album")
    albums            Album[]
}

Another example schema can be found here: #5782 (comment)

Problem 2: Multiple Cascade Paths

If you have more than two relations leaving from a model, they can't cross to the same model anywhere in their path.

model Album {
    id               Int      @id @default(autoincrement())
    FavoriteOf       Artist[] @relation("FK_fav_album")
    SecondFavoriteOf Artist[] @relation("FK_fav_album2")
}

model Artist {
    id                       Int   @id @default(autoincrement())
    favorite_album_id        Int
    second_favorite_album_id Int
    // this relation field makes it cyclic
    favorite_album           Album @relation(fields: [favorite_album_id], references: [id], name: "FK_fav_album")
    second_favorite_album    Album @relation(fields: [second_favorite_album_id], references: [id], name: "FK_fav_album2")
}

Problem 3: Self Relations

This is a special version of problem 1.

[janpio]

Internal writeup: https://www.notion.so/prismaio/Validation-of-referential-action-cycles-on-SQL-Server-c6fb884ed8524cf78ed6eb0665abef91