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 NOCHECK CONSTRAINT fk_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:
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.