Using the CHECK Constraint

[00:00:00]
>> So, let's go talk about constraints. We already saw this constraint here. Which is basically, a constraint is exactly what it sounds like, you're saying this column has some sort of rule about it as opposed to something like the body of text, right, that exists on recipe. There's just no rules for that, you can put literally whatever you want there.

[00:00:30]
Constraints is basically saying like, in order for this to be inserted here, it has to satisfy this rule before I'm gonna let you do that. So we've seen one. One was, That there must be a unique combination between recipe_id and ingredient_id, that's a constraint. If you remember unique that we did earlier on the title of ingredients, unique is considered a constraint as well.

[00:01:06]
Primary key, that's a constraint, technically. Foreign keys, technically a constraint, right, cuz a constraint like this has to exist in the other table. Let's do one for our types, right, cuz we either have meat, fruit, vegetable, or other. So we can basically say this has to exist amongst these things or I won't let it in here.

[00:01:37]
That's essentially an enumerated type. And I use that word kind of cautiously cuz there's actually a separate concept in Postgres called enumerated types that you could also use here. There's a bunch of ways to do it. I'm just gonna show you the way to do with this check thing.

[00:01:57]
So let's go ahead and type that into our table. We can say, ALTER TABLE ingredients. ADD CONSTRAINT type_enims or type_check_enums, whatever, it's just whatever you wanna have it show up as, doesn't matter. CHECK, and you're gonna say, type has to be IN. Has to be one of these, a meat, a fruit, a vegetable, and other, And that's it.

[00:02:48]
So now, we have a check constraint in here. So if I try and do something like this, which I'm just gonna copy that, INSERT INTO ingredients, where title, image, type. So this right here going into type is obviously not a type, right? It's not in our meat, fruit, vegetable, or other, it's going to fail, cuz it's gonna say, this failed that obviously not a type is not in our meat, fruit, vegetable, or other.

[00:03:25]
So it's just allowing you to set some rules and boundaries around your data. And at this point, it kind of bears mentioning. This is one of the primary points of using an SQL database, particularly Postgres or MySQL, one of these. Is that, if you have highly structured data with schema that have a lot of rules around it, like this can be this and can't be that, and should be this and can't be less than this, SQL is really good at stuff like this.

[00:03:54]
It's really good if you can give it specific rules of enforcement of those rules stay consistent. If you go into things like Redis, and Mongo DB, and some of these other ones, they have some ability to do some of that. But it's not necessarily as built for whereas SQL is like it just thrives on constraints and rules and stuff like that.

[00:04:11]
So, yeah, this is the primary use case of when you wanna use SQL.
>> I'm assuming that this would work similarly to the not null constraint, where you could probably give it a default if you had a type. I'm wondering if you had a type in there that didn't meet that constraint.

[00:04:30]
When you're adding a constraint, I'm assuming you can default that similarly.
>> Yeah.
>> Check, okay.
>> Well, hold on. The answer is it's gonna fail if we tried to do this, and we had a, I don't know, spice in there or something like that. I imagine and I've never tried this, there's something to say like, on fail do something like this, on conflict set to be a default.

[00:04:56]
But really, what you're gonna do is you just wanna go update all those yourself before you do that. So I'm showing you all these ALTER TABLE kind of commands just so you know that once you set a table, you don't have to living down, that you have to drop it to recreate it.

[00:05:10]
You can alter them, it's really expensive on big tables. I remember we went over to Reddit, we had to run database migrations from one schema to another. It would take hours, because Reddit has huge, huge tables as you might imagine. It has every comment that in every user and every upvote that's ever existed in there.

[00:05:32]
They're huge tables. So you really don't wanna run ALTER TABLE haphazardly, right? You need to plan for it. You probably has like scheduled downtime and things like that. So if you're adding a constraint that didn't exist before, in that case, I probably wouldn't even add it. It's probably not worth it.

[00:05:53]
But if I was gonna do it, what I would do is I would go update manually all the things that were in conflict, and then I would run the alter table.