1
00:00:00,120 --> 00:00:03,250
The next thing we're going to see are check constraints.

2
00:00:03,270 --> 00:00:05,130
These are extra fancy constraints.

3
00:00:05,130 --> 00:00:11,180
We can define basically custom constraints on a given column or columns, which we'll see in a moment.

4
00:00:11,190 --> 00:00:13,480
Well, we'll see that in a separate video coming up.

5
00:00:13,500 --> 00:00:20,940
So a simple example would be doing something like making sure that age column is greater than 18 or

6
00:00:20,940 --> 00:00:22,670
at a minimum, maybe it's just positive.

7
00:00:22,680 --> 00:00:26,190
We don't want to have a negative age for a user in the way that we do.

8
00:00:26,190 --> 00:00:28,380
This is when we define the column.

9
00:00:28,470 --> 00:00:35,380
Age is an integer, then we write, check and then in parentheses whatever expression we might want.

10
00:00:35,430 --> 00:00:37,410
Age greater than 18.

11
00:00:37,410 --> 00:00:42,310
And this is going to return true or false, which is going to be one or zero, right?

12
00:00:42,330 --> 00:00:47,700
If this returns one, then we pass that test and age is considered valid.

13
00:00:47,910 --> 00:00:54,030
But if it returns zero, then the age is invalid and the entire row would be invalid.

14
00:00:54,030 --> 00:00:56,070
We wouldn't insert that age.

15
00:00:56,070 --> 00:00:59,730
So let me show you that example or maybe another example.

16
00:01:00,660 --> 00:01:03,090
Let's do create table.

17
00:01:03,120 --> 00:01:12,360
I don't know, just users and we'll say users have to have a username that is var char one.

18
00:01:12,540 --> 00:01:12,960
I don't know.

19
00:01:12,960 --> 00:01:13,590
20.

20
00:01:13,950 --> 00:01:14,510
Sure.

21
00:01:14,520 --> 00:01:14,940
Not.

22
00:01:14,940 --> 00:01:15,510
No.

23
00:01:15,660 --> 00:01:18,600
And age has to be an integer.

24
00:01:19,080 --> 00:01:22,530
And what we've seen in the past is I could set that to be negative.

25
00:01:22,530 --> 00:01:25,650
So instead I'm going to say, Here's our custom check.

26
00:01:26,190 --> 00:01:28,590
Age must be greater than zero.

27
00:01:29,040 --> 00:01:30,120
Let's just do that.

28
00:01:30,150 --> 00:01:33,010
This example is 18, but this one is zero.

29
00:01:33,030 --> 00:01:35,370
It's the same concept, obviously, just a different number.

30
00:01:36,090 --> 00:01:37,800
All right, so let's create that table.

31
00:01:38,790 --> 00:01:47,880
Now, if I do an insert into users and I'll do username, comma, age values, and our username here

32
00:01:47,880 --> 00:01:49,170
will be blue.

33
00:01:49,170 --> 00:01:55,800
The cat, my cat blue and age will be I think she's in human years.

34
00:01:55,800 --> 00:01:57,210
She's like 50 or 60.

35
00:01:57,210 --> 00:01:58,170
She's getting up there.

36
00:01:58,560 --> 00:01:59,970
Well in cat years rather.

37
00:02:00,300 --> 00:02:01,530
Och I hit enter.

38
00:02:01,560 --> 00:02:02,970
No problem whatsoever.

39
00:02:02,970 --> 00:02:13,080
But if I instead tried to insert another user name like Colt Steel and I put my name as negative three,

40
00:02:13,950 --> 00:02:17,760
we get this check constraint users check one is violated.

41
00:02:18,120 --> 00:02:20,550
So this message doesn't really help us much.

42
00:02:20,550 --> 00:02:25,950
We will see how we can actually come up with a custom name for our constraints in the next video.

43
00:02:26,040 --> 00:02:29,220
But we do see that I got an error right?

44
00:02:29,220 --> 00:02:34,470
If I select star from users, that user is not inserted.

45
00:02:34,470 --> 00:02:35,490
Colt Steel is not in there.

46
00:02:35,490 --> 00:02:36,900
Negative three is not in there.

47
00:02:36,930 --> 00:02:39,270
Age has to be positive.

48
00:02:39,270 --> 00:02:40,560
So that part's working.

49
00:02:40,680 --> 00:02:43,710
What I'm going to show you next is how we can come up with a custom message.

50
00:02:43,710 --> 00:02:49,110
But before we do that, why don't we try one more example that's a little more complicated.

51
00:02:49,140 --> 00:02:57,360
I can do things like enforce that a username can not have a space in it, or even that it has to be

52
00:02:57,360 --> 00:02:58,140
a palindrome.

53
00:02:58,140 --> 00:02:59,160
I could do whatever I want.

54
00:02:59,160 --> 00:03:00,150
Let's try that one.

55
00:03:00,150 --> 00:03:03,960
Let's create a table called palindromes.

56
00:03:03,960 --> 00:03:05,160
How do you spell palindrome?

57
00:03:05,160 --> 00:03:06,390
Is it Palin?

58
00:03:06,390 --> 00:03:09,750
Palin I think it's palindromes like this.

59
00:03:09,750 --> 00:03:16,200
And there's going to be one column, which is word, and that's going to be a bar chart.

60
00:03:16,230 --> 00:03:18,230
Let's call it bar chart 100.

61
00:03:18,240 --> 00:03:21,480
And I want to make sure that it's the same forwards and backwards.

62
00:03:21,480 --> 00:03:28,770
So what I would do is write a check to see that the reverse, if you remember the reverse method or

63
00:03:28,770 --> 00:03:36,960
reverse string function, select reverse of Hello gives me ALAC in reverse.

64
00:03:36,960 --> 00:03:42,900
So I want to check that the reverse of the word is equal to the word.

65
00:03:43,530 --> 00:03:44,010
Right.

66
00:03:44,010 --> 00:03:50,970
So if I have a word like race car, which is a palindrome, the reverse of that is equal to the word

67
00:03:50,970 --> 00:03:51,800
itself.

68
00:03:51,810 --> 00:03:57,270
So this is silly, but I just want to show you we're not limited to numbers or to greater than, although

69
00:03:57,270 --> 00:04:02,910
that's a really common use case we can use between we can use the other operators we've seen and we

70
00:04:02,910 --> 00:04:04,530
can use different functions.

71
00:04:04,530 --> 00:04:08,340
So I'm checking to see that the reverse of the word is equal to the word.

72
00:04:08,460 --> 00:04:09,690
Let's see what happens.

73
00:04:09,690 --> 00:04:12,300
If I try and insert into that table.

74
00:04:12,300 --> 00:04:19,500
I need a semicolon insert into palindromes word values.

75
00:04:19,800 --> 00:04:21,570
Our first one will be race car.

76
00:04:22,410 --> 00:04:23,490
And that works.

77
00:04:24,810 --> 00:04:30,360
But what about something that is not a palindrome like mommy that is not going to work?

78
00:04:30,360 --> 00:04:33,660
And we see palindromes check one is violated.

79
00:04:34,050 --> 00:04:39,480
But if I instead did mom, that will work because it's the same forwards and backwards.

80
00:04:39,480 --> 00:04:44,310
So silly example, but yet another example of a check constraint.

81
00:04:44,310 --> 00:04:49,980
We can do whatever we want, assuming that we can write the correct expression that is going to be some

82
00:04:49,980 --> 00:04:53,790
sort of boolean true or false value, a one or a zero.

83
00:04:54,150 --> 00:04:54,720
All right.

84
00:04:54,720 --> 00:04:58,650
Next, I'm going to show you how we can customize the name of our constraints.

