Wondering why we need to add constraint SQL server with NOCHECK and how it works? Our experts have the answer, Read on to learn more. Our SQL Server Support Services team is here to lend a hand with your queries and issues.

Why should we add constraint SQL server with NOCHECK ?

Recently, one of our customers ran into trouble when they added Foreign Key constraints to their script. Fortunately, our SQL server experts were able to resolve the issue in no time. Before we dive into the solution, let’s take a look at why this error occurs.

According to industry experts, WITH CHECK / WITH NOCHECK and CHECK / NOCHECK have two purposes. The former deals with existing data when the command is run, while the latter applies only to new data.

While creating Foreign Key constraints, many people set the WITH CHECK / WITH NOCHECK option and forgo the CHECK / NOCHECK option. This results in the latter being set to CHECK automatically. This can be resolved easily by running an additional NOCHECK command to override the default CHECK value.

In other words, when we create a Foreign Key constraint with the WITH NOCHECK option, the constraint is not verified if it contains data that will conflict with the data currently in the table. Then it becomes a regular constraint.

The NOCHECK option comes into play when we are altering the table. After that, it acts as if we did not use NOCHECK constraint. Furthermore, we can permanently remove the constraint with DROP.

Additionally, our experts would like to point out that any foreign key constraint made using WITH NOCHECK constraint is deemed “not trusted”. In other words, SQL Server will not use it in the execution plan due to the possibility of inconsistent data.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

To wrap things up, we got a firsthand look at adding constraint SQL server with NOCHECK and more. We also got to learn the difference between WITH CHECK / WITH NOCHECK and CHECK / NOCHECK from our experts.