1

here are two tables in my Visual Studio 2015 project
Table 1: 

CREATE TABLE [dbo].[Player] (
    [Id]         INT           IDENTITY (1, 1) NOT NULL,
    [First_Name] NVARCHAR (50) NULL,
    [Last_Name]  NVARCHAR (50) NULL,
    [Nickname]   NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

and table 2: 

CREATE TABLE [dbo].[Match] (
    [Id]            INT      IDENTITY (1, 1) NOT NULL,
    [Time]          TIME (7) NULL,
    [Winner_Id]     INT      NOT NULL,
    [Loser_Id]      INT      NOT NULL,
    [Tournament_Id] INT      NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Player_Winner] FOREIGN KEY ([Winner_Id]) REFERENCES [dbo].[Player] ([Id]),
    CONSTRAINT [FK_Player_Loser] FOREIGN KEY ([Loser_Id]) REFERENCES [dbo].[Player] ([Id]),
    CONSTRAINT [FK_Match_Tournament] FOREIGN KEY ([Tournament_Id]) REFERENCES [dbo].[Tournament] ([Id])
);

What I want is to add ON DELETE CASCADE on the foreign keys in the Match table, but I am unable to create a delete cascade on the player foreign keys. I would like to be able to do this, because if a player is deleted, I would like all matches he had participated in to be deleted as well, both as a winner and as a loser. My backup solution would be to add a column in Match that holds a "forfeit" value, so matches with this flag is not counted. This flag could be set when a player is deleted. Any idea how to implement this?

Torstein Røsok
  • 11
  • 1
  • What happens when you try? – Rahul Mar 28 '17 at 13:12
  • @Rahul If I'm reading the log correctly, it's the "Foreign key constraint may cause cycles or multiple cascade paths" message. – Torstein Røsok Mar 28 '17 at 13:14
  • You need to DROP the key. See : http://stackoverflow.com/questions/93264/how-do-i-drop-a-foreign-key-in-sql-server – jdweng Mar 28 '17 at 13:19
  • jdweng, If they drop the key they will lose the connection via which their cascading deletes would be performed. How would this improve their situation? – toonice Mar 28 '17 at 13:27
  • Some clues may lie here - http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths – toonice Mar 28 '17 at 13:38

0 Answers0