It may cause loops or multiple cascade paths. Specify on delete no action or on update no action, or modify other foreign key constraints .,

It may cause loops or multiple cascade paths. Specify on delete no action or on update no action, or modify other foreign key constraints .,

Error message: it may cause loops or multiple cascade paths. Specify on delete no action or on update no action, or modify other foreign key constraints.

Cause: cascading deletion and cascading update are not allowed for a self-join table (the same table is connected to itself.

I. SQL statements

Create table DataClass (
    CID nvarchar(6) not null,
    ParentID nvarchar(6) null,
    CNAME nvarchar(50) not null,
    ENAME nvarchar(50) not null,
    DISCRIB nvarchar(200) null,
    DATATYPE smallint null,
    Constraint PK_DATACLASS primary key (CID)
)
Go

Create unique index IX_DataClass on DataClass (
ENAME ASC
)
Go

Alter table DataClass
Drop constraint FK_DataType_self
-- Error: May cause loops or multiple cascade paths. Please specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
--alter table DataClass
-- add constraint FK_DataType_self foreign key (ParentID)
-- references DataClass (CID)
-- on update cascade
--go

-- changed to:
Alter table DataClass
    Add constraint FK_DataType_self foreign key (ParentID)
       References DataClass (CID)
          On update NO ACTION
Go

 

Ii. Discovery

On update no action can be omitted because this mechanism is used by default.

The content is as follows:

USE [Ecology]
GO

Alter table [dbo]. [DataClass] with check add constraint [FK_DataType_self] foreign key ([ParentID])
REFERENCES [dbo]. [DataClass] ([CID])
GO

Alter table [dbo]. [DataClass] check constraint [FK_DataType_self]
GO

 

Sqlserver automatically omitting on update NO ACTION

 SQL constraints can be modified:

alter table DataClass
   add constraint FK_DataType_self foreign key (ParentID)
      references DataClass (CID)
go

You do not need to set this parameter in PowderDesigner.