Disable foreign key in SQL Server

If you want to disable foreign keys in SQL Server, this is how.

Once you have created the FOREIGN KEY foreign key in SQL Server, there will be instances where you need to disable them. Then use the ALTER TABLE command.

The syntax to disable foreign keys in SQL Server

 ALTER TAB LE ten_bang 
NOCHECK C ONSTRAINT fk_ten;

Variable name or variable value

ten_bang

The name of the table to which the foreign key has been created.

fk_ten

The name of the foreign key you want to disable.

For example

 CREATE TABL E sanpham 
( id_sanpham INT PRIMARY KEY,
ten_sanpham VARCHAR(50) NOT NULL,
phan_loai VARCHAR(25)
);

CREATE TABLE hangtonkho
( id_hangtonkho INT PRIMARY KEY,
id_sanpham INT NOT NULL,
soluong INT,
luong_toithieu INT,
luong_toida INT,
CONSTRAINT fk_htk_id_sanpham
FOREIGN KEY (id_sanpham)
REFERENCES sanpham (id_sanpham)
);

In this example, we created the parent table, sanpham, with the primary key including the information field id_sanpham. Then there is a child table named hangtonkho with a foreign key with deletion constraint. The CREATE TABLE statement creates a foreign key on the hangtonkho table named fk_htk_id_sanpham. The foreign key forms the relationship between the id_sanpham column in the hangtonkho table and id_sanpham in the sanpham table.

  1. Foreign Key (Cascade Delete) in SQL Server
  2. Foreign Key (Set Null) foreign key in SQL Server

If you want to disable the foreign key created, run the command below.

 ALTER TABLE hangtonkh o 
NOCHECK CONSTRAINT fk_htk_id_sanpham;

The example above uses the ALTER TABLE command to disable the foreign key fk_htk_id_sanpham created in the hangtonkho table.

Previous post: Delete the foreign key in SQL Server

The following article: Enable foreign key in SQL Server

4 ★ | 1 Vote