Using SET NULL and SET DEFAULT with Foreign Key Constraints

By Jeff Smith on 12 August 2008 | Tags: Database Design

Cascading Updates and Deletes, introduced with SQL Server 2000, were such an important, crucial feature that it is hard to imagine providing referential integrity without them. One of the new features in SQL Server 2005 that hasn't gotten a lot of press from what I've read is the new options for the ON DELETE and ON UPDATE clauses: SET NULL and SET DEFAULT. Let's take a look!

Introduction

First, consider an application with multiple Users and multiple "Themes" that the user can select from, to control how things look for them in the application:

create table Themes
(
	ThemeID int primary key,
	ThemeName varchar(100),
)

create table Users
(
	UserID int primary key,
	UserName varchar(100),
	ThemeID int constraint Users_ThemeID_FK references Themes(ThemeID) 
)

insert into Themes (ThemeID, ThemeName) values (1,'Default')
insert into Themes (ThemeID, ThemeName) values (2,'Winter')

insert into Users(UserID, UserName, ThemeID) values (1,'JSmith',null)
insert into Users(UserID, UserName, ThemeID) values (2,'Ted',1)
insert into Users(UserID, UserName, ThemeID) values (3,'Mary',2)

In the above, we have a simple schema with a table of Themes and a table of Users. The Users table has a "ThemeID" column that stores the User's Theme preference. Note that a User is not required to have a Theme selected; if they don't, they just get the application's default look.

Now, suppose that for some reason, a Theme is removed from the system, so we need to delete the Theme from the table. If any User has that Theme selected, we will get an error when trying to delete the Theme due to the foreign key constraint on the column:

delete from Themes where ThemeID=2

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__Users__ThemeID__23F3538A". 
The conflict occurred in database "PlayGround", table "dbo.Users", column 'ThemeID'.
The statement has been terminated.

In order to delete this Theme, we must first unassign it from any user that has it selected.

On Delete Cacade

In some situations, "ON DELETE CASCADE" does this beautifully. For example, if there is a table that relates multiple Users to multiple Themes, then ON DELETE CASCADE is just what you are after and any rows relating Users to the Theme just deleted will be removed.

In our situation, however, what would happen if we had used ON DELETE CASCADE in our foreign key constraint?

-- remove the existing constraint:
alter table users drop constraint Users_ThemeID_FK

-- re-create it:
alter table users add constraint Users_ThemeID_FK 
    foreign key (ThemeID) references Themes(ThemeID)on delete cascade

go

delete from Themes where ThemeID =2

select * from Users

UserID      UserName        ThemeID
----------- --------------- -----------
1           JSmith          NULL
2           Ted             1

(2 row(s) affected)

Wait a minute! Where's Mary? Deleting a Theme deleted all Users who had that Theme selected! Surely, this is not what we want.

So, what should happen? There are really only two options we have if we'd like to be able to delete Themes but still maintain referential integrity. If a Theme is deleted, for all Users using that Theme we'd like to either:

a) Set their ThemeID to NULL

or

b) Assign a different, "default" ThemeID

On Delete Set Null

Setting foreign key references to NULL is very easy to do with SQL 2005 and above. We simply set the foreign key's ON DELETE clause to SET NULL and it will work just as we'd expected:

-- remove the existing constraint:
alter table users drop constraint Users_ThemeID_FK

-- This time, create it with on delete set null:
alter table users add constraint Users_ThemeID_FK 
    foreign key (ThemeID) references Themes(ThemeID) on delete set null

-- Add our data back in
insert into Themes (ThemeID, ThemeName) values (2,'Winter')
insert into Users(UserID, UserName, ThemeID) values (3,'Mary',2)

-- And now delete ThemeID 2 again:
delete from Themes where ThemeID =2

-- Let's see what we've got:
select * from Users

UserID      UserName          ThemeID
----------- ----------------- -----------
1           JSmith            NULL
2           Ted               1
3           Mary              NULL

(3 row(s) affected)

And there you go! Mary is still there this time, and by deleting ThemeID 2, it simply set all foreign key references to that value to NULL. This can be very handy in cases like this, and it can make things much easier to manage if used properly.

On Delete Set Default

Another option is to provide a DEFAULT value for the column, and use the ON DELETE SET DEFAULT. For example, suppose the default ThemeID for all Users is ThemeID 1. If we set the column's default value to 1 in our table definition, and use ON DELETE SET DEFAULT, when the foreign table has rows deleted, the column's default is used to replace the (now deleted) foreign key value.

Let's try it out; we'll just delete the Users table and re-create it, since that's probably easiest at this point:


drop table Users
go

create table Users
(
	UserID int primary key,
	UserName varchar(100),
	ThemeID int default 1 constraint Users_ThemeID_FK 
	    references Themes(ThemeID) on delete set default
)
go

-- Add ThemeID 2 back in:
insert into Themes (ThemeID, ThemeName) values (2,'Winter')

-- Re-create our users again:
insert into Users(UserID, UserName, ThemeID) values (1,'JSmith',null)
insert into Users(UserID, UserName, ThemeID) values (2,'Ted',1)
insert into Users(UserID, UserName, ThemeID) values (3,'ARod',2)

-- Now, delete ThemeID 2:
delete from Themes where ThemeID = 2

-- And let's see what we've got:
select * from Users

UserID      UserName        ThemeID
----------- --------------- -----------
1           JSmith          1
2           Ted             1
3           Mary  	          1

Notice this time that Mary's Theme has been set to the default value -- 1 -- as soon as the Theme she had been assigned was deleted. Pretty cool!

On Update

We've only looked at ON DELETE, but ON UPDATE supports the same options. I don't see the ON UPDATE situations being as useful, since CASCADE seems to make the most sense when updating a foreign key value. For example, if ThemeID 2 gets changed to 4, then I think it usually makes sense to update all references to also be ThemeID 4, as opposed to setting those Themes back to NULL or some default value. But, it is an option and may be the way to go for you depending on your needs.

Summary

As always, with any sort of ON DELETE or ON UPDATE clause, use with care -- referential integrity is there for a reason, and having an error message tell you that you cannot UPDATE or DELETE something due to a foreign key reference is one of the great features of a relational database, even though it may seem "annoying" sometimes; it really helps to ensure that your data is clean, consistent and valid. Cascading updates and deletes and setting foreign keys to Defaults or NULL automatically are nice options to have, but it can be very dangerous since it all happens silently and automatically "behind the scenes". Be sure that you really test and completely understand how these options work before using them. Writing little scripts like I have done here is the way to go when learning about new features, so I encourage you to do the same.