The other types of constraints are referred to as check constraints. They limit the data by defining a logical operation that checks the state of the data prior to allowing an insert or update to the table. The logical operation is not against other tables. The logic can be against multiple columns in the same table. Let’s start with a simple example. The business has determined that the transactions it’s recording will never be less than $10. As part of the business definition, they would like a constraint put in place that limits the TransactionAmount values in the Finance.FinancialTransaction table to only accept values that are greater than $10. Let’s create this constraint using the GUI and again, in the next post, using T-SQL.
Check Constraint with the GUI
We’ll need to open the Table Designer window for the Finance.FinancialTransaction table. When that is open, if you look at the toolbar and hover your mouse, there is a button labeled “Manage Check Constraints.” Click on this button and the Check Constraints window will open. This window will be completely blank the first time you open it. It only has three buttons; Add, Delete, and Close. If you notice it will prompt you to use the Add button. Clicking that Add button will create a default set of values in a check constraint as shown in Figure 7.4:
You can see that the prompt in the upper right corner of the window suggests what you need to do to get started. You must define the Expression that will contain the logical test that you’re going to run against the data.
You can type directly into the Expression row on the right where all the data entry is done or you can click on the ellipsis to open an editing window. The editing window is basically just a very large text box. The expression in our case is: TransactionAmount > 10.0. I use the decimal place to be sure that the data types match. If I had not entered a decimal, then it might do a conversion for me and that could cause performance issues down the road. Once the expression is defined, I’m going to also change the Name of the constraint to CheckTransactionAmount. I generally find a clear, English, description is better than using code abbreviations like CK for Check. The other options are fine as the defaults. Click Close and then save the table and this constraint will be in place. The final constraint looks like this:
Conclusion
As you can see, it’s pretty straight forward. The concepts of constraints are not radically different if we’re talking foreign keys or check constraints. The next post will show how to do this using T-SQL.