Failure creating a migration with MSSQL: `Introducing FOREIGN KEY constraint '...' on table '...' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.` #5782

2color · 2021-02-22T13:26:41Z

Bug description

I attempted to use Prisma Migrate (2.17.0) to create a migration from the Prisma schema below and got the following error:

Error: Database error: Error querying the database: 'Introducing FOREIGN KEY constraint 'FK__Comment__postId' on table 'Comment' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.' on server 46f430d4d1fc executing  on line 57 (code: 1785, state: 0, class: 16)
   0: migration_core::api::ApplyMigrations
             at migration-engine/core/src/api.rs:51

It seems that the combination of defaulting to cascading deletes and a circular relation (between User : Post : Comment : User) causes the SQL to be invalid.

How to reproduce

Create the Prisma schema
Run npx prisma migrate dev --preview-feature to generate and apply the migration

This is where the error shows up.

I was able to resolve this by updating the FK__Comment__postId constraint in the migration SQ.

-- CreateTable
CREATE TABLE [dbo].[User] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__User__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [email] NVARCHAR(1000) NOT NULL,
    [name] NVARCHAR(1000),
    CONSTRAINT [PK__User__id] PRIMARY KEY ([id]),
    CONSTRAINT [User_email_unique] UNIQUE ([email])
);

-- CreateTable
CREATE TABLE [dbo].[Post] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__Post__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [title] NVARCHAR(1000) NOT NULL,
    [content] NVARCHAR(1000),
    [published] BIT NOT NULL CONSTRAINT [DF__Post__published] DEFAULT 0,
    [authorId] INT NOT NULL,
    CONSTRAINT [PK__Post__id] PRIMARY KEY ([id])
);

-- CreateTable
CREATE TABLE [dbo].[Comment] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__Comment__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [comment] NVARCHAR(1000) NOT NULL,
    [writtenById] INT NOT NULL,
    [postId] INT NOT NULL,
    CONSTRAINT [PK__Comment__id] PRIMARY KEY ([id])
);

-- CreateTable
CREATE TABLE [dbo].[Tag] (
    [id] INT NOT NULL IDENTITY(1,1),
    [tag] NVARCHAR(1000) NOT NULL,
    CONSTRAINT [PK__Tag__id] PRIMARY KEY ([id]),
    CONSTRAINT [Tag_tag_unique] UNIQUE ([tag])
);

-- CreateTable
CREATE TABLE [dbo].[_TagToPost] (
    [A] INT NOT NULL,
    [B] INT NOT NULL,
    CONSTRAINT [_TagToPost_AB_unique] UNIQUE ([A],[B])
);

-- CreateIndex
CREATE INDEX [_TagToPost_B_index] ON [dbo].[_TagToPost]([B]);

-- AddForeignKey
ALTER TABLE [dbo].[Post] ADD CONSTRAINT [FK__Post__authorId] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__writtenById] FOREIGN KEY ([writtenById]) REFERENCES [dbo].[User]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
-- I changed this one from "ON DELETE CASCADE ON UPDATE CASCADE" to "ON DELETE NO ACTION ON UPDATE NO ACTION" which resolved it
ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__postId] FOREIGN KEY ([postId]) REFERENCES [dbo].[Post]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- AddForeignKey
ALTER TABLE [dbo].[_TagToPost] ADD CONSTRAINT [FK___TagToPost__A] FOREIGN KEY ([A]) REFERENCES [dbo].[Post]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[_TagToPost] ADD CONSTRAINT [FK___TagToPost__B] FOREIGN KEY ([B]) REFERENCES [dbo].[Tag]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

Expected behavior

It should generate an executable migration.

Prisma information

Prisma schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["microsoftSqlServer"]
}

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

model User {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now())
  email     String    @unique
  name      String?
  comments  Comment[]
  posts     Post[]
}

model Post {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now())
  title     String
  content   String?
  published Boolean   @default(false)
  authorId  Int
  author    User      @relation(fields: [authorId], references: [id])
  comments  Comment[]
  tags      Tag[]     @relation("TagToPost")
}

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

model Tag {
  id    Int    @id @default(autoincrement())
  tag   String @unique
  posts Post[] @relation("TagToPost")
}

Environment & setup

OS: Mac OS
Database: Microsoft SQL Server (Docker image: mcr.microsoft.com/mssql/server:2019-latest)
Node.js version: v14.15.0
Prisma version:

➜  sql-server git:(latest) npx prisma version
Environment variables loaded from prisma/.env
prisma               : 2.17.0
@prisma/client       : 2.17.0
Current platform     : darwin
Query Engine         : query-engine 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/prisma/node_modules/@prisma/engines/query-engine-darwin)
Migration Engine     : migration-engine-cli 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/prisma/node_modules/@prisma/engines/migration-engine-darwin)
Introspection Engine : introspection-core 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/prisma/node_modules/@prisma/engines/introspection-engine-darwin)
Format Binary        : prisma-fmt 3c463ebd78b1d21d8fdacdd27899e280cf686223 (at node_modules/prisma/node_modules/@prisma/engines/prisma-fmt-darwin)
Studio               : 0.353.0
Preview Features     : microsoftSqlServer

The text was updated successfully, but these errors were encountered:

pantharshit00 · 2021-04-13T11:00:22Z

This still reproducible in 2.21.0-dev.38

pimeys · 2021-06-03T14:20:43Z

This is going to be solved with the PR prisma/prisma-engines#1947

What this means for this problem is, if getting an error from a cascade loops, you can manually define the onDelete and onUpdate rules in the relation to not cascade.

Instructions on how to use this, and a place to give feedback: #7816

janpio · 2021-06-29T10:19:51Z

This has now been released as a preview feature behind a preview feature flag. You can read about it in the release notes for 2.26.0: https://github.com/prisma/prisma/releases/tag/2.26.0 If you have any feedback, please use this issue: #7816

janpio · 2021-07-01T09:08:15Z

This should now work with the referentialActions preview feature enabled. We should confirm this is the case.

pimeys · 2021-07-01T09:11:58Z

Let's say this a bit differently: if getting this error, we have now tools to replace the Cascade with some other action.

janpio · 2021-07-04T12:53:29Z

Upgrading to 2.26.0 and activating the referentialActions preview feature changes the default on these relations/foreign keys from ON DELETE CASCADE to ON DELETE NO ACTION. Unfortunately this is not enough to solve the problem:

The initial schema:

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["microsoftSqlServer", "referentialActions"]
}


model User {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now())
  email     String    @unique
  name      String?
  comments  Comment[]
  posts     Post[]
}

model Post {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now())
  title     String
  content   String?
  published Boolean   @default(false)
  authorId  Int
  author    User      @relation(fields: [authorId], references: [id])
  comments  Comment[]
  tags      Tag[]     @relation("TagToPost")
}

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

model Tag {
  id    Int    @id @default(autoincrement())
  tag   String @unique
  posts Post[] @relation("TagToPost")
}

Leads to this migration with migrate dev --create-only:

-- CreateTable
CREATE TABLE [dbo].[User] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__User__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [email] NVARCHAR(1000) NOT NULL,
    [name] NVARCHAR(1000),
    CONSTRAINT [PK__User__id] PRIMARY KEY ([id]),
    CONSTRAINT [User_email_unique] UNIQUE ([email])
);

-- CreateTable
CREATE TABLE [dbo].[Post] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__Post__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [title] NVARCHAR(1000) NOT NULL,
    [content] NVARCHAR(1000),
    [published] BIT NOT NULL CONSTRAINT [DF__Post__published] DEFAULT 0,
    [authorId] INT NOT NULL,
    CONSTRAINT [PK__Post__id] PRIMARY KEY ([id])
);

-- CreateTable
CREATE TABLE [dbo].[Comment] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__Comment__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [comment] NVARCHAR(1000) NOT NULL,
    [writtenById] INT NOT NULL,
    [postId] INT NOT NULL,
    CONSTRAINT [PK__Comment__id] PRIMARY KEY ([id])
);

-- CreateTable
CREATE TABLE [dbo].[Tag] (
    [id] INT NOT NULL IDENTITY(1,1),
    [tag] NVARCHAR(1000) NOT NULL,
    CONSTRAINT [PK__Tag__id] PRIMARY KEY ([id]),
    CONSTRAINT [Tag_tag_unique] UNIQUE ([tag])
);

-- CreateTable
CREATE TABLE [dbo].[_TagToPost] (
    [A] INT NOT NULL,
    [B] INT NOT NULL,
    CONSTRAINT [_TagToPost_AB_unique] UNIQUE ([A],[B])
);

-- CreateIndex
CREATE INDEX [_TagToPost_B_index] ON [dbo].[_TagToPost]([B]);

-- AddForeignKey
ALTER TABLE [dbo].[Post] ADD CONSTRAINT [FK__Post__authorId] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__writtenById] FOREIGN KEY ([writtenById]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__postId] FOREIGN KEY ([postId]) REFERENCES [dbo].[Post]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[_TagToPost] ADD CONSTRAINT [FK___TagToPost__A] FOREIGN KEY ([A]) REFERENCES [dbo].[Post]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[_TagToPost] ADD CONSTRAINT [FK___TagToPost__B] FOREIGN KEY ([B]) REFERENCES [dbo].[Tag]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

Deploying that with migrate dev still leads to this error message:

C:\Users\Jan\Documents\throwaway\sqlServerCycles>npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db" - SQL Server

Error: P3006

Migration `20210704124210_init` failed to apply cleanly to the shadow database. 
Error:
Database error
Error querying the database: 'Introducing FOREIGN KEY constraint 'FK__Comment__postId' on table 'Comment' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.' on server EC2AMAZ-HEFESC9 executing  
on line 57 (code: 1785, state: 0, class: 16)
   0: sql_migration_connector::validate_migrations
             at migration-engine\connectors\sql-migration-connector\src\lib.rs:322
   1: migration_core::api::DevDiagnostic
             at migration-engine\core\src\api.rs:89

or migrate deploy:

C:\Users\Jan\Documents\throwaway\sqlServerCycles>npx prisma migrate deploy
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db" - SQL Server

1 migration found in prisma/migrations
Error: P3018

A migration failed to apply. New migrations can not be applied before the error is recovered from. Read more about how to resolve migration issues in a production database: https://pris.ly/d/migrate-resolve

Migration name: 20210704124210_init

Database error code: 1785

Database error:
Introducing FOREIGN KEY constraint 'FK__Comment__postId' on table 'Comment' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

When you then add a onUpdate: NoAction to the correct relation in the schema (where the CLI output does not really help you to figure that out without a lot of SQL knowledge) like this:

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["microsoftSqlServer", "referentialActions"]
}


model User {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now())
  email     String    @unique
  name      String?
  comments  Comment[]
  posts     Post[]
}

model Post {
  id        Int       @id @default(autoincrement())
  createdAt DateTime  @default(now())
  title     String
  content   String?
  published Boolean   @default(false)
  authorId  Int
  author    User      @relation(fields: [authorId], references: [id])
  comments  Comment[]
  tags      Tag[]     @relation("TagToPost")
}

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

model Tag {
  id    Int    @id @default(autoincrement())
  tag   String @unique
  posts Post[] @relation("TagToPost")
}

-   post        Post     @relation(fields: [postId], references: [id])
+   post        Post     @relation(fields: [postId], references: [id], onUpdate: NoAction)

Will give you this SQL now with:

-- CreateTable
CREATE TABLE [dbo].[User] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__User__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [email] NVARCHAR(1000) NOT NULL,
    [name] NVARCHAR(1000),
    CONSTRAINT [PK__User__id] PRIMARY KEY ([id]),
    CONSTRAINT [User_email_unique] UNIQUE ([email])
);

-- CreateTable
CREATE TABLE [dbo].[Post] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__Post__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [title] NVARCHAR(1000) NOT NULL,
    [content] NVARCHAR(1000),
    [published] BIT NOT NULL CONSTRAINT [DF__Post__published] DEFAULT 0,
    [authorId] INT NOT NULL,
    CONSTRAINT [PK__Post__id] PRIMARY KEY ([id])
);

-- CreateTable
CREATE TABLE [dbo].[Comment] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__Comment__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [comment] NVARCHAR(1000) NOT NULL,
    [writtenById] INT NOT NULL,
    [postId] INT NOT NULL,
    CONSTRAINT [PK__Comment__id] PRIMARY KEY ([id])
);

-- CreateTable
CREATE TABLE [dbo].[Tag] (
    [id] INT NOT NULL IDENTITY(1,1),
    [tag] NVARCHAR(1000) NOT NULL,
    CONSTRAINT [PK__Tag__id] PRIMARY KEY ([id]),
    CONSTRAINT [Tag_tag_unique] UNIQUE ([tag])
);

-- CreateTable
CREATE TABLE [dbo].[_TagToPost] (
    [A] INT NOT NULL,
    [B] INT NOT NULL,
    CONSTRAINT [_TagToPost_AB_unique] UNIQUE ([A],[B])
);

-- CreateIndex
CREATE INDEX [_TagToPost_B_index] ON [dbo].[_TagToPost]([B]);

-- AddForeignKey
ALTER TABLE [dbo].[Post] ADD CONSTRAINT [FK__Post__authorId] FOREIGN KEY ([authorId]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__writtenById] FOREIGN KEY ([writtenById]) REFERENCES [dbo].[User]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__postId] FOREIGN KEY ([postId]) REFERENCES [dbo].[Post]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;

-- AddForeignKey
ALTER TABLE [dbo].[_TagToPost] ADD CONSTRAINT [FK___TagToPost__A] FOREIGN KEY ([A]) REFERENCES [dbo].[Post]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[_TagToPost] ADD CONSTRAINT [FK___TagToPost__B] FOREIGN KEY ([B]) REFERENCES [dbo].[Tag]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

- ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__postId] FOREIGN KEY ([postId]) REFERENCES [dbo].[Post]([id]) ON DELETE NO ACTION ON UPDATE CASCADE;
+ ALTER TABLE [dbo].[Comment] ADD CONSTRAINT [FK__Comment__postId] FOREIGN KEY ([postId]) REFERENCES [dbo].[Post]([id]) ON DELETE NO ACTION ON UPDATE NO ACTION;

You can successfully migrate the schema:

C:\Users\Jan\Documents\throwaway\sqlServerCycles>npx prisma migrate dev
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Datasource "db" - SQL Server

The following migration(s) have been applied:

migrations/
  └─ 20210704124950_init/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client (2.26.0) to .\node_modules\@prisma\client in 1.07s

While there is now a manual way to fix this, I don't think this is enough to close this issue yet. We now have the tools to build a real solution, but will have to figure out what that looks like. (ideas: schema validation, better error message)

janpio · 2021-07-05T09:31:45Z

Recap of an internal discussion about this:

We have 3 options:

We don't do anything about these right now
We try to parse the error and turn it into a Prisma relevant error that tells you where you need to update the referential action in the schema (table name + FK name => model + relation field)
We implement detection of cycles in our schema validation as well and prevent migration of the schema ([SQL Server] add validation for disallowed relationships (e.g. cyclic) #4580)

Discussion of these options:

1 has the problem that the database unfortunately is in a pretty ugly state after trying to run the migration - all the table creations or changes are already done, but then the foreign key creation or update fails and the migration ends. You have to clean that up yourself, before you can update the schema file to include the required changes and create a new migration to apply
2 only improves the situation of 1 by making the "update the schema file to include the required changes" a bit, and we could possibly give some instruction how to clean up after yourself
3 is pretty complicated and takes a lot of effort, but would really solve this problem once and for all as a user could not run a migration with a schema in that state and would be protected against the mess and the error message.

Additional information:

Options 1 and 2 could become a lot nicer, if we decide to wrap the SQL Server migrations into a transaction as suggested in Wrap migrations into a transaction, if possible #7641 - this would make them more viable as an acceptable user experience.

Further thoughts about how to get out of the error situation, even with #7641 implemented:

The problem is that the error message is not easy to parse, as it is about SQL things and not Prisma things.
Figuring out which relation in which model to edit, is super hard to describe in docs or error message
A better way might be to tell people to use Introspection to update their schema:
1. Migration fails because of error
2. We catch the error and wrap it into some explanation and link to our docs with a nice explanation
3. Explanation tells people to edit the generated migrations SQL directly
4. Run migration again
5. Run introspection to update the schema to contain the correct referential action!

janpio · 2021-07-07T08:59:30Z

We are doing it the right way and will add detection of cyclic referential actions via: #4580 This will be added while SQL Server is still in preview.

The question if we wrap migrations in transactions is handled separately: #7641

2color added topic: migrate topic: sql server Microsoft SQL Server labels Feb 22, 2021

janpio added the team/schema Issue for team Schema. label Feb 22, 2021

pantharshit00 added bug/2-confirmed Bug has been reproduced and confirmed. kind/bug A reported bug. labels Feb 26, 2021

Jolg42 added the process/candidate label Mar 2, 2021

tomhoule removed the process/candidate label Mar 3, 2021

janpio assigned pantharshit00 Apr 13, 2021

pantharshit00 removed their assignment Apr 13, 2021

janpio added the process/candidate label Apr 13, 2021

tomhoule self-assigned this Apr 14, 2021

tomhoule removed the process/candidate label Apr 14, 2021

tomhoule added this to the 2.22.0 milestone Apr 14, 2021

tomhoule added the topic: cascade label Apr 14, 2021

This comment has been minimized.

Sign in to view

tomhoule removed their assignment May 11, 2021

pimeys self-assigned this May 20, 2021

pimeys mentioned this issue May 20, 2021

Referential actions in the Prisma Schema Language prisma/prisma-engines#1947

Merged

5 tasks

janpio added the topic: referential actions label Jun 3, 2021

tomhoule modified the milestones: 2.22.0, 2.26.0 Jun 17, 2021

pimeys closed this as completed in prisma/prisma-engines#1947 Jun 22, 2021

janpio assigned pantharshit00 Jul 1, 2021

janpio mentioned this issue Jul 4, 2021

migrate dev can not be used to fix "invalid" migrations over time #8064

Open

janpio reopened this Jul 4, 2021

janpio mentioned this issue Jul 4, 2021

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

Closed

janpio unassigned pantharshit00 Jul 4, 2021

tomhoule modified the milestones: 2.26.0, 2.28.0 Jul 15, 2021

thebiglabasky removed this from the 2.28.0 milestone Jul 28, 2021

Jolg42 added this to the 2.29.0 milestone Jul 29, 2021

pimeys mentioned this issue Jul 30, 2021

SQL Server: Detect referential action cycles prisma/prisma-engines#2113

Merged

pimeys closed this as completed in prisma/prisma-engines#2113 Aug 2, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Failure creating a migration with MSSQL: `Introducing FOREIGN KEY constraint '...' on table '...' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.` #5782

Failure creating a migration with MSSQL: `Introducing FOREIGN KEY constraint '...' on table '...' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.` #5782

2color commented Feb 22, 2021 •

edited

pantharshit00 commented Apr 13, 2021

This comment has been minimized.

pimeys commented Jun 3, 2021 •

edited by janpio

janpio commented Jun 29, 2021

janpio commented Jul 1, 2021

pimeys commented Jul 1, 2021

janpio commented Jul 4, 2021

janpio commented Jul 5, 2021 •

edited

janpio commented Jul 7, 2021 •

edited

Failure creating a migration with MSSQL: Introducing FOREIGN KEY constraint '...' on table '...' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. #5782

Failure creating a migration with MSSQL: Introducing FOREIGN KEY constraint '...' on table '...' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. #5782

Comments

2color commented Feb 22, 2021 • edited

Bug description

How to reproduce

Expected behavior

Prisma information

Environment & setup

pantharshit00 commented Apr 13, 2021

This comment has been minimized.

pimeys commented Jun 3, 2021 • edited by janpio

janpio commented Jun 29, 2021

janpio commented Jul 1, 2021

pimeys commented Jul 1, 2021

janpio commented Jul 4, 2021

janpio commented Jul 5, 2021 • edited

janpio commented Jul 7, 2021 • edited

Failure creating a migration with MSSQL: `Introducing FOREIGN KEY constraint '...' on table '...' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.` #5782

Failure creating a migration with MSSQL: `Introducing FOREIGN KEY constraint '...' on table '...' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.` #5782

2color commented Feb 22, 2021 •

edited

pimeys commented Jun 3, 2021 •

edited by janpio

janpio commented Jul 5, 2021 •

edited

janpio commented Jul 7, 2021 •

edited