SQL Server: Disable a foreign key

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


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.


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

ALTER TABLE table_name

Parameters or Arguments

The name of the table where the foreign key has been created.
The name of the foreign key that you wish to disable.


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:

( 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.