SQL Server CHECK Constraints

   

    CHECK Constraints is another example of hidden gems in SQL Server world. Many people don't know or use them.

   CHECK Constraints will keep your data clean. SQL Server provides many system data types to store data. Usually we want to store some kind of formatted data (phone number, IP address, social security number, etc..) and we do everything we can to keep this data clean by all kind of validation in client side but in some way invalid data finds its way to SQL Server.
SQL Server can check the data before it inserts/updates the table If the data format doesn't match, SQL Server will refuse it. We used to do this with User-Defined Data types before, Microsoft will remove User-Defined Data types in future and recommends to use Unique and Check Constraints instead.
 
   CHECK Constraints enforce domain integrity by limiting the values that are accepted by one or more columns. You can create a CHECK Constraint to check if the value is valid for SQL to accept it.

 Let's create one to accept username. We don't want any whitespace at the beginning or at the end of our username, also we don't want any test or admin accounts in our username.

SQL Server will check if Username contains any whitespace in the beginning or at the end of the username value, also it will check if the username is admin or test before it inserts the row to the table.

Here what happens, if I try to insert a value with whitespace.