Hey everyone, welcome back. In this video, part 10 of topic three in our database class, I'm going to show you how you can use the SQL Check Constraint to try to protect the quality of the data in your database tables. All right, so yeah, let's take a look at the Check Constraint. This will be a new one for us, but the basic idea is that we want to check whether or not a proposed value for a column is acceptable. Then based on whatever that notion of acceptability means to us, so we can essentially establish a new constraint that will limit the acceptable values for any of the columns in a table. And again, the purpose here is to try to protect the quality of the data in the database. So let's take a look at this first example here. And in this case, we're adding a Check Constraint that will evaluate whether the proposed start and end dates appear to be valid. So in this case, we're adding a Check Constraint to a table called project. So this table is going to hold information about different projects. And if you imagine, we all understand some of the characteristics of a project, it would not be reasonable for the start date on a project to be later than the end date. Or conversely, it would not be reasonable for the end date to happen before the start date. So this is just a way that we can ensure that when someone is entering new rows into our project table, that is, they're recording information about new projects that the relationship between the start date and the end date makes sense. The start date should be less than the end date, assuming that each project lasts at least one day. Otherwise, we might have to use less than or equal to. So if this Check Constraint were in place, and we tried to add a new project record to the project table, before the database would add that information to the table and make it permanent, it would do this check. And it would say, all right, is the start date less than the end date. If so, everything's fine. And we can add that row to the table. If not, we will get an error message and that proposed row of data will not be added to the table. They're by ensuring that this potential mistake does not get entered and become a part of our official organizational data. Okay, so here's a check. In this case, we're looking for a less than course you can do greater than, less than or equal to greater than or equal to, etc. And here's another example of this. In this case, down here, we are adding a new Check Constraint to our employee table. And in this case, what we're doing is we are checking to ensure that whenever we add a new employee record, the value of the higher date for the new employee is greater than or equal to, say, the first of January 2015. So if we imagine a scenario where maybe we started our company on the first of January 2015, then a Check Constraint like this would make a lot of sense because it wouldn't be feasible to hire someone to work at our company before the company existed. So if we have this sort of Check Constraint in place and we hire a new person and let's say that we're entering 2022 for their higher date and someone accidentally types in like 2011, one one here instead of two two, then this Check Constraint would catch that error. And the person trying to save the invalid higher date would get an error message and they would then have to say, oh, wow, because that's not a valid higher date because the company didn't exist at that time. So you can imagine all kinds of uses for these types of Check Constraint, they're very, very useful for ensuring that the data that are being added to the tables fall within an acceptable logical or meaningful range. I wouldn't make any sense if we're, I don't know, like recording our customers' date of birth, it wouldn't really make any sense for us to allow values of the 1400s for that. Right. Nobody alive today was born in the 1400s. Similarly, it wouldn't make any sense for us to allow values of their birth year that would be like in the 2100s. So if someone was trying to store information about a customer and they say their birth date is the, I don't know, 5th of January, 2142. Well, that's obviously invalid and our Check Constraint would catch that in the scenario. So these are very useful. Again, in this case, we're just continuing to build out our set of knowledge on the DDL, new types of constraints. Right. Remember, we already know that primary keys are constraints, foreign keys are constraints, and we saw how we can add those after the fact by using an altar statement as well. We just have add here. And as we saw just a moment ago, if we wanted to get rid of a Check Constraint, we would just change this keyword here to drop. Right. So instead of add, we would say drop. And obviously we wouldn't have to provide any details either. All we have to do is provide the name of the constraint to prevent it. So if we wanted to get rid of this Check Constraint, assuming it already existed, we would say altar table project, drop Constraint, Check Project Dates. And that's all we would need to get rid of that.