I have two tables, Entity and Arrest. The Entity table is where I store all my people. The arrest table is where I handle criminal arrests. In the arrest table there is multiple FK's to the Entity Table IE (Who was arrested, who did the arresting, and a couple more). If I delete the entity I want to null out all of the entity FK in the arrest table. However, I get error's when I add "On Delete set null" action to the my second FK.
Here is the FK code and the error I get.
ALTER TABLE[arrest].[Arrest] WITH CHECK ADD CONSTRAINT[FK_arrest_TreatedBy] FOREIGN KEY([TreatedBy])
REFERENCES[entity].[Entity]([ID])
ON DELETE SET NULL ON UPDATE NO ACTION
GO
ALTER TABLE[arrest].[Arrest] CHECK CONSTRAINT[FK_arrest_TreatedBy]
GO
ALTER TABLE[arrest].[Arrest] WITH CHECK ADD CONSTRAINT[FK_arrest_arrestee] FOREIGN KEY([Arrestee])
REFERENCES entity.entity([ID])
ON DELETE SET null ON UPDATE NO ACTION
GO
ALTER TABLE[arrest].[Arrest] CHECK CONSTRAINT[FK_arrest_arrestee]
GO
Introducing FOREIGN KEY constraint 'FK_arrest_arrestee' on table 'Arrest' may cause cycles or multiple cascade paths.Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.