question

AndrewMeador-0563 avatar image
0 Votes"
AndrewMeador-0563 asked AndrewMeador-0563 answered

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

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!

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndrewMeador-0563 avatar image
0 Votes"
AndrewMeador-0563 answered

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!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.