Using ON UPDATE SET DEFAULT in SQL Server

Posted on by Ian

In SQL Server, foreign keys are essential for maintaining referential integrity between tables. When creating foreign keys, we have a number options available to us when it comes to defining what should happen in the event that the related data in the parent table changes. One such option is ON UPDATE SET DEFAULT.

This article will explain what this option does, provide an example of its usage, and offer a practical scenario where this feature can be useful.

What is ON UPDATE SET DEFAULT?

The ON UPDATE SET DEFAULT option is used when defining a foreign key constraint in SQL Server. This option ensures that if the value in the referenced column of the parent table is updated, the foreign key column in the child table is automatically set to its default value.

To use this option, the foreign key column in the child table must have a default value defined. If no default value is set, SQL Server will not allow the ON UPDATE SET DEFAULT constraint. That said, if the column is nullable, and no default value is defined, then the default value will be NULL (unless it’s a timestamp column, in which case the next timestamp value is inserted).

Syntax

To implement this option, we simply append ON UPDATE SET DEFAULT to the foreign key definition.

For example, here’s the basic syntax for adding a foreign key with ON UPDATE SET DEFAULT:

ALTER TABLE [ChildTable]
ADD CONSTRAINT [ConstraintName]
FOREIGN KEY ([ForeignKeyColumn]) 
REFERENCES [ParentTable] ([PrimaryKeyColumn])
ON UPDATE SET DEFAULT;

We can see that the last line is ON UPDATE SET DEFAULT, which means that the foreign key uses this option for any updates to the parent table.

Example Scenario

Consider an e-commerce system with two tables:

  1. ShippingMethods: Stores information about the available shipping methods.
  2. Orders: Stores details about customer orders, including the shipping method used.

In this scenario, if a shipping method is updated or changed, we might want all orders that used the old method to be set to a default shipping method, like “Standard Shipping” (which could be ShippingMethodID = 1).

Let’s walk through a step-by-step example:

Step 1: Create the Tables

First, create the ShippingMethods and Orders tables:

CREATE TABLE ShippingMethods (
    ShippingMethodID INT PRIMARY KEY,
    MethodName NVARCHAR(100)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    ShippingMethodID INT DEFAULT 1,
    FOREIGN KEY (ShippingMethodID) 
        REFERENCES ShippingMethods(ShippingMethodID) 
        ON UPDATE SET DEFAULT
);

In this example:

  • ShippingMethods contains ShippingMethodID and MethodName.
  • Orders contains OrderID, OrderDate, and ShippingMethodID, which references ShippingMethodID in ShippingMethods. The foreign key is set to ON UPDATE SET DEFAULT, and the column has a default value of 1.

Step 2: Insert Sample Data

Insert initial data into both tables and select it:

-- Populate ShippingMethods table
INSERT INTO ShippingMethods (ShippingMethodID, MethodName)
VALUES (1, 'Standard Shipping'), (2, 'Express Shipping'), (3, 'Overnight Shipping');

-- Populate Orders table
INSERT INTO Orders (OrderID, OrderDate, ShippingMethodID)
VALUES (101, '2024-08-21', 2), 
       (102, '2024-08-22', 3),
       (103, '2024-08-23', 2);

-- Select all data
SELECT * FROM ShippingMethods;
SELECT * FROM Orders;

Here:

  • Three orders are created using Express Shipping and Overnight Shipping.
  • Three shipping methods are created: Standard Shipping (ID = 1), Express Shipping (ID = 2), and Overnight Shipping (ID = 3).

Here’s the output of the SELECT queries:

ShippingMethodID  MethodName        
----------------  ------------------
1                 Standard Shipping 
2                 Express Shipping  
3                 Overnight Shipping

OrderID  OrderDate                 ShippingMethodID
-------  ------------------------  ----------------
101      2024-08-21T00:00:00.000Z  2               
102      2024-08-22T00:00:00.000Z  3               
103      2024-08-23T00:00:00.000Z  2

Step 3: Update the Parent Table

Suppose the company decides to retire Express Shipping and repurpose it under a different ID. If we update the ShippingMethodID from 2 to 4, we want any orders using the old ID (2) to automatically switch to the default Standard Shipping ID (1).

Let’s update the parent table:

UPDATE ShippingMethods
SET ShippingMethodID = 4
WHERE ShippingMethodID = 2;

Since we have an ON UPDATE SET DEFAULT constraint, any orders associated with the old ShippingMethodID (2) will have their ShippingMethodID set to 1 (the default value defined on the foreign key in the Orders table).

Step 4: Verify the Results

Finally, let’s check the data in the tables to see the effect of the update:

SELECT * FROM ShippingMethods;
SELECT * FROM Orders;

Output:

ShippingMethodID  MethodName        
----------------  ------------------
1                 Standard Shipping 
3                 Overnight Shipping
4                 Express Shipping  

OrderID  OrderDate                 ShippingMethodID
-------  ------------------------  ----------------
101      2024-08-21T00:00:00.000Z  1               
102      2024-08-22T00:00:00.000Z  3               
103      2024-08-23T00:00:00.000Z  1

Here’s what happened:

  • Orders 101 and 103, which initially used Express Shipping (ShippingMethodID = 2), have been automatically reassigned to the default Standard Shipping (ShippingMethodID = 1).
  • Order 102, which used Overnight Shipping (ShippingMethodID = 3), remains unchanged.

Other Use Cases for ON UPDATE SET DEFAULT

The ON UPDATE SET DEFAULT option is ideal when you want to avoid leaving foreign key fields without a valid reference while ensuring that they point to a meaningful default value. A couple of other use cases could include:

  • Customer Service Applications: If a customer’s account is deactivated or merged, setting the related foreign keys to a default “Guest” or “Archived” ID ensures continuity in records.
  • Inventory Systems: If a product ID changes due to an update, related transactions can be set to a default “Unassigned” ID to maintain data integrity without losing transaction history.

NULL Default Values

There are some cases where ON UPDATE SET DEFAULT will result in the column being set to NULL. For example:

  • If the column has been defined with a DEFAULT value of NULL, then it will end up having NULL after ON UPDATE SET DEFAULT does it’s thing.
  • If we don’t specify a default value for the column, and it’s nullable, then SQL Server will make its default value NULL. Therefore, this will result in the column being updated to NULL in any applicable updates.

If you want to explicitly set the column to NULL, it might be better to use ON UPDATE SET NULL.