How to configure cascading delete on 2 FKs that make up a composite primary key? Or trigger?

Question

So I have a table:

CREATE TABLE [dbo].[juncLocationLocation](
 [FromLocationID] [int] NOT NULL,
 [ToLocationID] [int] NOT NULL,
 [Bearing] [decimal](5, 2) NOT NULL,
 CONSTRAINT [PK_juncLocationLocation] PRIMARY KEY CLUSTERED 
(
 [FromLocationID] ASC,
 [ToLocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Currently this table has these FK's defined:

ALTER TABLE [dbo].[juncLocationLocation]  WITH CHECK ADD  CONSTRAINT [FK_juncLocationLocation_FromLocationID_Location_ID] FOREIGN KEY([FromLocationID])
REFERENCES [dbo].[Location] ([ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[juncLocationLocation] CHECK CONSTRAINT [FK_juncLocationLocation_FromLocationID_Location_ID]
GO

ALTER TABLE [dbo].[juncLocationLocation]  WITH CHECK ADD  CONSTRAINT [FK_juncLocationLocation_ToLocationID_Location_ID] FOREIGN KEY([ToLocationID])
REFERENCES [dbo].[Location] ([ID])
GO

ALTER TABLE [dbo].[juncLocationLocation] CHECK CONSTRAINT [FK_juncLocationLocation_ToLocationID_Location_ID]
GO

As you can see - the FK_juncLocationLocation_FromLocationID_Location_ID is defined with casacading deletes.

SQL Server will not allow creating the other FK with cascading deletes - it gives this error:

Msg 1785, Level 16, State 0, Line 31 - Introducing FOREIGN KEY constraint 'FK_juncLocationLocation_ToLocationID_Location_ID' on table 'juncLocationLocation' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

Is there a way to configure this so that deleting a Location from the Location table will delete rows in the juncLocationLocation table whether it's ID is in either column FromLocationID or ToLocationID using cascading delete methods? I was thinking about creating a Delete trigger on the Location table to do it - but wanted to see if there is a better way first.

Thanks!

Answer 1

That's what I thought might be the case. Seem of with separate FK's that they cannot enforce their own cascade delete - but, oh well. Thanks for the confirmation and solution!

Answer 2

There is no way to make both of those foreign key constraints ON DELETE CASCADE. You are right, the way to do this is with an INSTEAD OF trigger on the Location table. The trigger could look like

CREATE TRIGGER INSTEADOF_Location 
ON dbo.Location
INSTEAD OF DELETE AS
BEGIN
DELETE FROM dbo.juncLocationLocation WHERE FromLocationID in (SELECT ID FROM deleted);
DELETE FROM dbo.juncLocationLocation WHERE ToLocationID in (SELECT ID FROM deleted);
DELETE FROM dbo.Location WHERE ID in (SELECT ID FROM deleted);
END
go

Tom