Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Another way that we can increase the reliability, the integrity of our data, is to apply what are called check constraints. A check constraint is a simple idea. If we have some data such as Quantity where we can look at that and understand this really unacceptable range for this to be in, even though this column is actually being defined as an int, we don't need all that an int can give us. Because an integer can support from roughly -2 billion to plus 2 billion, it is quite unlikely that someone will order -2 billion quantities of a unit.
So let's say I want to enforce some rules on this column that says Quantity must be greater than 0 and less than 51 say. Well, I do this by applying a check constraint to the table. I am going to right-click anywhere in the Designer area. It really doesn't matter. And I have a section called Check Constraints. Right now it's saying there are no check constraints on this table. I am going to add one. It's typically called CK_ the name of the table. What we need to type in is an expression. In this case, it's quite simple. Quantity > 0 AND Quantity < 51. Close.
We now have a check constraint defined on the table. It is considered a table change. So I see the asterisk. I have got to save that. Close this down. Now, when I go into that table and I say change one of the existing rows, 50 is accepted as a change and I can click off that row. 51 will actually throw an error here. The update statement conflicted with the check constraint. I also can't put a 0 Quantity and I can't put a negative Quantity.
Check constraints can easily be removed. I need to escape out of here before it tries to apply those changes. I could go back into the Design view, again right-clicking anywhere in the Designer, because the check constraints are defined for the table. They're not defined specifically on a column. It's just this expression. You could have one check constraint that has multiple columns in it. Quantity above a certain amount if ProductID in a particular range. But if we wanted to, we could just come in here and hit the Delete button to remove the check constraint from the table, but I'm going to keep it.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64867 Viewers
80 Video lessons · 124403 Viewers
52 Video lessons · 60327 Viewers
59 Video lessons · 46153 Viewers