Enforce Custom Logic with Check Constraints in Postgres
What if your tables need custom constraints? A movie should only receive 1-5 stars, never 0 or 6. Price after tax should always equal the sum of the price plus tax. Check constraints help you enforce your own custom logic.
[00:00] We have a movies table. We don't have any kind of constraint that limits us on the data that we can actually put in here. Let's look at just a normal insert statement here, and we'll add in "Barton Fink" and we'll say it was released on this day. I don't think that's true.
[00:24] Let's give it a CountStars of 6, even though, we want CountStars to really just be 1 to 5. We don't have any kind of constraint here yet. We're going to insert that. Now, we can see Barton Fink violates our constraint.
[00:37] We could also have done it maybe even with stars of...or that was director ID of 0but we want stars of 0That's a shame, isn't it? Why don't we go ahead and delete from movies where the ID is 4, 5, or 6. We'll get rid of our Barton Finks and we'll add a constraint.
[01:03] Alter table, movies. We'll add a constraint. We have to name it, as always. We'll say CountStars greater than 0This is a check constraint, and then, we have to put the check in parentheses, so we'll say CountStars is greater than 0That will force us to do that. We'll also say that it's less than 6, because we don't want six stars for anything.
[01:34] We'll back up a bit and just get ourselves to the point where we had run this. Let's try it with 6 and that fails. Let's try it with 5. That's OK. Let's try it with 0That fails.
[01:48] Try it with 1. That's OK. Let's try it with an arbitrarily high number. 10. -1. We already have started to see that our constraints have fallen into place, and these are constraints that we define for our table.