SQL Server: Disable a foreign key

Learn how to disable a foreign key in SQL Server with syntax and examples.

Description

Once you have created a foreign key in SQL Server, you may encounter a situation where you are required to disable the foreign key. You can do this using the ALTER TABLE statement in SQL Server.

Syntax

The syntax to disable a foreign key in SQL Server (Transact-SQL) is:

ALTER TABLE table_name
NOCHECK CONSTRAINT fk_name;

Parameters or Arguments

table_name

The name of the table where the foreign key has been created.

fk_name

The name of the foreign key that you wish to disable.

Example

Let's look at an example of how to disable a foreign key in SQL Server (Transact-SQL) using the ALTER TABLE statement.

For example, if you had created a foreign key as follows:

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 NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
);

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.

If we then wanted to disable the foreign key, we could execute the following command:

ALTER TABLE inventory
NOCHECK CONSTRAINT fk_inv_product_id;

This foreign key example would use the ALTER TABLE statement to disable the constraint called fk_inv_product_id on the inventory table.