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.
ALTER TABLE [dbo].[QuoteType] WITH CHECK ADD CONSTRAINT [FK_QuoteType_Quote] FOREIGN KEY([QuoteId]) REFERENCES [dbo].[Quote] ([Id]) ON DELETE Set Null