Foreign key set null action

Thursday, August 01, 2013

In some case the set null action is usefull

For example I have a table QuoteTypes wich defines the types for the Quotes in the table Quotes. You want a foreign key with a set null action because with a delete of a QuoteType this will automatically set all referenced fields to null.

That's nice. This will maintain the data-integrity and prefents a lot of programming for the programmer.

Example:

ALTER TABLE [dbo].[QuoteType]  WITH CHECK 
ADD CONSTRAINT [FK_QuoteType_Quote] FOREIGN KEY([QuoteId])
REFERENCES [dbo].[Quote] ([Id])
ON DELETE Set Null