I have a database design like below. I have 3 tables Compartment, CompartmentRelation and CompartmentRelationType . CompartmentRelation table keeps the other compartments around the selected compartment (below,above,behind,infront,etc). CompartmentRelationType keeps the position. Think that i have compartments in the Compartment table named comp-1, comp-2, comp-3, comp-4 and insert the the compartments above comp-1 as comp-2,comp-3 in CompartmentRelation as below. Problem is that setting delete action as cascade for the column RelatedCompId in CompartmentRelation table throw the excaption as

Unable to create relationship 'FK_CompartmentRelation_Compartment1'.  
Introducing FOREIGN KEY constraint 'FK_CompartmentRelation_Compartment1' on table 'CompartmentRelation' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

Which way should i follow ?

Compartment

• comp-1
• comp-2
• comp-3
• comp-4

Compartment Relation

• comp-1 -> comp-2

• comp-1 -> comp-3

  CREATE TABLE [dbo].[Compartment] (
       [Id]           INT            IDENTITY (1, 1) NOT NULL,
       [Name]         NVARCHAR (500) NOT NULL,
       CONSTRAINT [PK_Compartment] PRIMARY KEY CLUSTERED ([Id] ASC),
  
   CREATE TABLE [dbo].[CompartmentRelation] (
   [Id]             INT IDENTITY (1, 1) NOT NULL,
   [CompId]         INT NOT NULL,
   [RelationTypeId] INT NOT NULL,
   [RelatedCompId]  INT NOT NULL,
   CONSTRAINT [PK_CompartmentRelation] PRIMARY KEY CLUSTERED ([Id] ASC),
   CONSTRAINT [FK_CompartmentRelation_CompartmentRelationType] FOREIGN KEY ([RelationTypeId]) REFERENCES [dbo].[CompartmentRelationType] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT [FK_CompartmentRelation_Compartment1] FOREIGN KEY ([RelatedCompId]) REFERENCES [dbo].[Compartment] ([Id]),
   CONSTRAINT [FK_CompartmentRelation_Compartment] FOREIGN KEY ([CompId]) REFERENCES [dbo].[Compartment] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE);
  
   CREATE TABLE [dbo].[CompartmentRelationType] (
       [Id]       INT            IDENTITY (1, 1) NOT NULL,
       [Name]     NVARCHAR (200) NOT NULL,
       [NameLan1] NVARCHAR (200) NOT NULL,
       [NameLan2] NVARCHAR (200) NULL,
       CONSTRAINT [PK_CompartmentRelationType] PRIMARY KEY CLUSTERED ([Id] ASC)
   );
  

CodePudding user response：

Problem is that setting delete action as cascade for the column RelatedCompId in CompartmentRelation table throw the excaption as

Unable to create relationship 'FK_CompartmentRelation_Compartment1'.
Introducing FOREIGN KEY constraint 'FK_CompartmentRelation_Compartment1' on table 'CompartmentRelation' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

The basic issue appears to be that deletes from table Compartment (and updates to its PKs) can cascade to CompartmentRelation through two different foreign keys. If you intend to support Compartments being related to themselves, then that's end-of-story for cascading from Compartment to CompartmentRelation -- you can't do it.^* If you intend to forbid self relationships then you could try adding a check constraint to CompartmentRelation to enforce that prohibition, though I'm not at all sure that SQL Server will take that into account.

If SQL Server won't accept the cascading deletes then you have at least three options:

1. Make it an application responsibility to clean up compartment relationships before deleting compartments. (And don't cascade.)

2. Create triggers to handle relationship deletion when compartments are deleted. (And don't cascade.)

3. Create a stored procedure for deleting compartments, and make it handle the needed relationship deletions. (And don't cascade.)

Which way should i follow ?

Whichever of those makes the most sense for your application. All have advantages and disadvantages.

Additionally,

• Do not cascade updates of surrogate key columns, especially when the key values are machine generated, as all yours are. Those keys should never be updated in the first place, and if an attempt were ever made to update one then it would be better for the DB to reject it, for whatever reason, than to accept it.

• You probably don't want to cascade deletions of CompartmentRelationType to ComponentRelation. Including such cascading allows for deleting all the relations of a given type by deleting the type itself, but such a cascade is more likely to be performed mistakenly than intentionally, and if it were performed mistakenly then the resulting data loss would be significant. It's probably better to make the application delete all those relations explicitly if that's what it really means to do, and otherwise to reject deletion of types that are in use by existing relations.

^*Technically, you could do it by cascading from only one of the two FKs with Compartment, but it seems unlikely that such a half-measure would serve your purposes.