If we just rely on the database (or the middleware for that matter) to apply validation then the user won’t know that a problem has occurred until they send the data over the network.
This is not just user unfriendly, but may have performance implications too.
In this article, we will talk about CHECK constraints.
You may well disagree with this position, but I hope I can persuade you of its virtues – or at least that you might find the discussion interesting.
This is not to say that all validation and error handling should be done in the database.
More significantly, if we want to build usable systems then we have to put validation in the user interface.
We tend to think of validation as something that constrains or inhibits the user; but used effectively it can help users to avoid making mistakes in the first place, and provide useful advice for correcting errors that occur.
Consider the following example: It is required to set a restriction to the Salary column so that it stores only positive values not exceeding $150,000.
The conditional statement will look as follows: (Salary In the Table Designer section, you can set up the rules to enforce the constraint.
The T-SQL Script that does the same is very simple.
It is very interesting that when the constraint is enabled, the world CHECK is used twice – WITH CHECK CHECK CONSTRAINT.
Consider the following example: It is required to create a table that stores data about bank customers and fill it with test data.
The table will include the following columns: Customer Id, First Name, Last Name, Status, Phone, City, State, and Zip.
Foreign Key and Check Constraints are two types of constraints that can be disabled or enabled when required.