SQL Server: Foreign Keys with set null on delete

In this post explains how to use Foreign Keys with "set null on delete" in SQL Server with syntax and examples.

What is a foreign key with "Set NULL on delete" in SQL Server?

A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to NULL. The records in the child table will not be deleted in SQL Server.

A foreign key with set null on delete can be created using either a CREATE TABLE statement or an ALTER TABLE statement.

Create Foreign key with set null on delete - Using CREATE TABLE statement

Syntax

The syntax for creating a foreign key with set null on delete using a CREATE TABLE statement in SQL Server (Transact-SQL) is:

CREATE TABLE child_table
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE SET NULL
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
);

child_table

The name of the child table that you wish to create.

column1, column2

The columns that you wish to create in the table. Each column must have a datatype. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

fk_name

The name of the foreign key constraint that you wish to create.

child_col1, child_col2, ... child_col_n

The columns in child_table that will reference a primary key in the parent_table.

parent_table

The name of the parent table whose primary key will be used in the child_table.

parent_col1, parent_col2, ... parent_col3

The columns that make up the primary key in the parent_table. The foreign key will enforce a link between this data and the child_col1, child_col2, ... child_col_n columns in the child_table.

ON DELETE SET NULL

It specifies that the child data is set to NULL when the parent data is deleted. The child data is NOT deleted.

ON UPDATE

Optional. It specifies what to do with the child data when the parent data is updated. You have the options of NO ACTION, CASCADE, SET NULL, or SET DEFAULT.

NO ACTION

It is used in conjunction with ON DELETE or ON UPDATE. It means that no action is performed with the child data when the parent data is deleted or updated.

CASCADE

It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is either deleted or updated when the parent data is deleted or updated.

SET NULL

It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is set to NULL when the parent data is deleted or updated.

SET DEFAULT

It is used in conjunction with ON DELETE or ON UPDATE. It means that the child data is set to their default values when the parent data is deleted or updated.

Example

Let's look at an example of how to create a foreign key with set null on delete in SQL Server (Transact-SQL) using the CREATE TABLE statement.

For example:

CREATE TABLE products
( product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE SET NULL
);

In this foreign key example, we've created our parent table as the products table. The products table has a primary key that consists of the product_id field.

Next, we've created a second table called inventory that will be the child table in this foreign key example. We have used the CREATE TABLE statement to create a foreign key on the inventory table called fk_inv_product_id. The foreign key establishes a relationship between the product_id column in the inventory table and the product_id column in the products table.

For this foreign key, we have specified the ON DELETE SET NULL clause which tells SQL Server to set the corresponding records in the child table to NULL when the data in the parent table is deleted. So in this example, if a product_id value is deleted from the products table, the corresponding records in the inventory table that use this product_id will have the product_id set to NULL.

Now one important thing about setting up this foreign key!!! Since this foreign key will set the product_id field to NULL on delete in the inventory table, you need to make sure that you have set up the product_id column in the child table to a nullable column. If you set the column to a NOT NULL in the CREATE TABLE statement, you will receive the following error message:

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT <strong>NOT NULL</strong>,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE SET NULL
);

Msg 1761, Level 16, State 0, Line 1
Cannot create the foreign key "fk_inv_product_id" with the SET NULL referential action, because one or more referencing columns are not nullable.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

So be sure to define your product_id in the inventory table as a nullable field as follows so that SQL Server can set the column to NULL when the foreign key constraint needs to.

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE SET NULL
);

Command(s) completed successfully.

Create a foreign key with set null on delete - Using ALTER TABLE statement

Syntax

The syntax for creating a foreign key with set null on delete using an ALTER TABLE statement in SQL Server (Transact-SQL) is:

ALTER TABLE child_table
ADD CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE SET NULL;

child_table

The name of the child table that you wish to modify.

fk_name

The name of the foreign key constraint that you wish to create.

child_col1, child_col2, ... child_col_n

The columns in child_table that will reference a primary key in the parent_table.

parent_table

The name of the parent table whose primary key will be used in the child_table.

parent_col1, parent_col2, ... parent_col3

The columns that make up the primary key in the parent_table. The foreign key will enforce a link between this data and the child_col1, child_col2, ... child_col_n columns in the child_table.

ON DELETE SET NULL

Specifies that the child data is set to NULL when the parent data is deleted. The child data is NOT deleted.

Example

Let's look at an example of how to create a foreign key with set null on delete in SQL Server (Transact-SQL) using the ALTER TABLE statement.

For example:

ALTER TABLE inventory
ADD CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE SET NULL;

In this foreign key example, we've created a foreign key on the inventory table called fk_inv_product_id that references the products table based on the product_id field.

For this foreign key, we have specified the ON DELETE SET NULL clause which tells SQL Server to update the corresponding records in the child table to NULL when the data in the parent table is deleted. So in this example, if a product_id value is deleted from the products table, the corresponding records in the inventory table that use this product_id will have the product_id column set to NULL.