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!