The next thing we're going to see are check constraints.
These are extra fancy constraints.
We can define basically custom constraints on a given column or columns, which we'll see in a moment.
Well, we'll see that in a separate video coming up.
So a simple example would be doing something like making sure that age column is greater than 18 or
at a minimum, maybe it's just positive.
We don't want to have a negative age for a user in the way that we do.
This is when we define the column.
Age is an integer, then we write, check and then in parentheses whatever expression we might want.
Age greater than 18.
And this is going to return true or false, which is going to be one or zero, right?
If this returns one, then we pass that test and age is considered valid.
But if it returns zero, then the age is invalid and the entire row would be invalid.
We wouldn't insert that age.
So let me show you that example or maybe another example.
Let's do create table.
I don't know, just users and we'll say users have to have a username that is var char one.
I don't know.
20.
Sure.
Not.
No.
And age has to be an integer.
And what we've seen in the past is I could set that to be negative.
So instead I'm going to say, Here's our custom check.
Age must be greater than zero.
Let's just do that.
This example is 18, but this one is zero.
It's the same concept, obviously, just a different number.
All right, so let's create that table.
Now, if I do an insert into users and I'll do username, comma, age values, and our username here
will be blue.
The cat, my cat blue and age will be I think she's in human years.
She's like 50 or 60.
She's getting up there.
Well in cat years rather.
Och I hit enter.
No problem whatsoever.
But if I instead tried to insert another user name like Colt Steel and I put my name as negative three,
we get this check constraint users check one is violated.
So this message doesn't really help us much.
We will see how we can actually come up with a custom name for our constraints in the next video.
But we do see that I got an error right?
If I select star from users, that user is not inserted.
Colt Steel is not in there.
Negative three is not in there.
Age has to be positive.
So that part's working.
What I'm going to show you next is how we can come up with a custom message.
But before we do that, why don't we try one more example that's a little more complicated.
I can do things like enforce that a username can not have a space in it, or even that it has to be
a palindrome.
I could do whatever I want.
Let's try that one.
Let's create a table called palindromes.
How do you spell palindrome?
Is it Palin?
Palin I think it's palindromes like this.
And there's going to be one column, which is word, and that's going to be a bar chart.
Let's call it bar chart 100.
And I want to make sure that it's the same forwards and backwards.
So what I would do is write a check to see that the reverse, if you remember the reverse method or
reverse string function, select reverse of Hello gives me ALAC in reverse.
So I want to check that the reverse of the word is equal to the word.
Right.
So if I have a word like race car, which is a palindrome, the reverse of that is equal to the word
itself.
So this is silly, but I just want to show you we're not limited to numbers or to greater than, although
that's a really common use case we can use between we can use the other operators we've seen and we
can use different functions.
So I'm checking to see that the reverse of the word is equal to the word.
Let's see what happens.
If I try and insert into that table.
I need a semicolon insert into palindromes word values.
Our first one will be race car.
And that works.
But what about something that is not a palindrome like mommy that is not going to work?
And we see palindromes check one is violated.
But if I instead did mom, that will work because it's the same forwards and backwards.
So silly example, but yet another example of a check constraint.
We can do whatever we want, assuming that we can write the correct expression that is going to be some
sort of boolean true or false value, a one or a zero.
All right.
Next, I'm going to show you how we can customize the name of our constraints.