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.