We have teased it enough. We have got to talk about check constraints domains. If you think back to when we were talking about numbers, I told you that there's no way to declare a column based on its data type only as going from zero on, it actually goes from negative 2.1 billion to positive 2.1 billion for example.

It would be nice to say, actually I don't ever need those negative numbers. Let's shift all of that go from zero to 4.2 billion. Let's get double the range 'cause we only need positive numbers. That's just not possible in Postgres, we can restrict the downside say everything must be zero or greater. In other databases, you can declare a column as an unsigned integer everything gets treated as positive you get your entire range. Your entire set of bites goes towards positive numbers. Not the case here in Postgres.

In the video about characters, we talked about character varying text I said don't use the fixed width character column. Instead let's put a check constraint on it. That's what we're going to do right now. There are lots of different types of constraints, for now we're just going to constrain our discussion to talk about check constraints. Let's do that. In this example, we have two columns that definitely need a little bit of constraint. This should be greater than zero. Just because I'm telling you this should be equal to five characters, that is a little bit of business logic that's what we're going to work with. Now, the price should be greater than zero. That makes perfect sense to me. The easiest way to do this is to say check price greater than zero.

If we run that then we try to insert a negative one a value of foo, 'cause we haven't done anything here yet, negative one is going to trigger an error. It violates check constraint, check example, price check, then it tells you the failing row. This is pretty good. We have already enforced a little bit of data integrity, a little bit of business logic, a little bit of domain logic. We've enforced that such that bad data can't get in. We can make this a little bit nicer. We can give this a name that either working amongst our teams or God forbid this shows up to a user, ah, at least it'll have a better name. While we are working with it or when we see it in our error logs, it would be nice to know, what was that all about? Let's see if we can give this a pretty name. I'm just going to drop this table we can start over from the beginning. That table is gone now. We'll leave that there. That table is gone now.

Instead of just going straight from the data type to check, we can throw the word constraint here in the middle say, price must be positive. It's a little wordy, it is communicative. Now if we try to insert negative one, we see that we're violating the check constraints still, we do get a little bit nicer name. I like that. I'm sure that my coworkers will like that when it's super late at night I'm digging through the logs, I'm definitely going to like that. Now moving on, let's hop down here. We can say, should I give this one a name? Nah, we're just going to keep going. We can say check length of abbreviation is equal to five.

Now let's drop it recreate it let's make this valid. Try to insert foo it says example abbreviation check. There's our ugly name popping back up, that's fine. If we were to do four, five characters, that should work. Six characters that shouldn't work. This is great. I like this far, these are referred to as column constraints because they sit right next to the columns data type they reference a single column. It is a best practice if you are going to reference multiple columns to move it down to a table constraint. Every column constraint can be written as a table constraint. Not every table constraint can be written as a column constraint. Let me show you what I'm talking about.

If we were, let's drop this again. If we were to come down here say abbreviation text comma. Now we're like going to maybe go start a new column, instead we're going to start a new constraint. Let's get rid of that just for fun. Now we have taken that column constraint moved it down now it is a table constraint. If we run this again, we should still see that it is being enforced we're all good. This, far what I've done right here, mostly just a stylistic preference. When you start, when you start having, let's throw another, let's say that the discount price is numeric, is numeric.

I'm going to throw a constraint on here as well. I'm just going to say price must be positive. We're going to drop that. Now both price discount price contain column level. Whoops, contain column level constraints that they both must be greater than zero. However, we might want to enforce, at the database level, we might want to enforce that price is greater than discount price. That needs to come down to a table level constraint, price is greater than discount price. I don't think it is illegal to declare one like this at the column level, it is a bad practice. You won't go to jail, it's not a good idea. If you have a check constraint that references more than one column, go ahead move that down to a table constraint. Now if we run that, we need to drop that first. If we run that, the table worked just fine.

If we were to insert discount price, let's make the regular price 10 the discount price eight foo is still five characters. That's great news. Let's test a few things. Negative eight, no good, can't do that. Discount price check fails, negative 10, no good. Importantly, if the regular price is less than the discounted price, that also fails. Check constraints are awesome for enforcing data integrity. Now you could get into a religious battle or a flame war on Reddit about how much of your business logic should you be putting in the database. Good luck. Have fun. I do have a few opinions when it comes to enforcing data integrity like this, I say put it in the database. There's kind of in my mind a difference between business logic integrity, data integrity, is saying that the price must be greater than zero, is that business logic? It doesn't feel like it to me, I know that that's wishy-washy, enforcing that the price is greater than zero or that this abbreviation must be five characters long. That feels like data integrity. That feels like the world of data integrity to me, instead of the world of business logic.

I don't like putting complex triggers, for example, into my database to do a bunch of business logic. This to me is on an entirely different level I feel super comfortable putting this kind of stuff into the actual database layer instead of the application layer, especially if not all of your data updating inserting goes through the application, then you need these checks in the database. One thing you do need to remember about constraints these check constraints is they can't reference other tables. You can't reference another table from a check constraint. In fact you can't reference another row besides the one that is being updated or inserted.

Finally, you do have to drop recreate the check constraint. You can't just alter it. I think you can alter it to change the name, you can't alter it to change the actual logic of it. You can do the drop recreate in a single statement, such that you don't have some sort of timing error where somebody could put some bad data in there. Check constraints are awesome. Talk with your team about what you think belongs in the app versus the database. When it, is data integrity flavored? My vote is put that in the database.