1 00:00:00,090 --> 00:00:06,070 In this video, we're going to answer one question What is going on with this column right here? 2 00:00:06,090 --> 00:00:08,880 When we showed a table's description. 3 00:00:09,210 --> 00:00:13,260 So if we take a look at our cat's table, describe cats. 4 00:00:13,680 --> 00:00:16,230 This column right here, it says null. 5 00:00:16,230 --> 00:00:17,520 Yes and yes. 6 00:00:17,520 --> 00:00:19,560 So Field, that's the name of the column. 7 00:00:19,560 --> 00:00:21,690 Name is a var 50 characters. 8 00:00:21,690 --> 00:00:22,920 Age is an integer. 9 00:00:22,920 --> 00:00:23,670 No. 10 00:00:23,670 --> 00:00:24,060 Yes. 11 00:00:24,060 --> 00:00:24,390 No. 12 00:00:24,390 --> 00:00:24,720 Yes. 13 00:00:24,720 --> 00:00:25,980 What does that mean? 14 00:00:26,340 --> 00:00:33,750 Well, what this means is that the null value is permitted in this particular column in name and in 15 00:00:33,750 --> 00:00:34,350 age. 16 00:00:34,350 --> 00:00:38,460 NULL is a value in my SQL that means no value. 17 00:00:39,120 --> 00:00:43,170 It represents the lack of a value for a given row. 18 00:00:43,200 --> 00:00:49,890 It basically means if we see null set to yes, that name can be empty for a row or age can be empty, 19 00:00:49,890 --> 00:00:52,710 or in fact both could be missing entirely. 20 00:00:52,740 --> 00:00:55,320 In other words, it means the value is unknown. 21 00:00:55,320 --> 00:00:59,700 But it's really important to know this is different than something like zero. 22 00:00:59,880 --> 00:01:01,320 Zero is a number. 23 00:01:01,320 --> 00:01:02,490 It is a value. 24 00:01:02,490 --> 00:01:06,750 NULL means that there is no information, there is no value at all. 25 00:01:06,780 --> 00:01:08,820 It's a MySQL concept here. 26 00:01:09,180 --> 00:01:12,270 So we could do something like this right now with our cat's table. 27 00:01:12,300 --> 00:01:16,350 I could say I'm inserting a new cat that has a name and no age. 28 00:01:16,350 --> 00:01:17,460 I'll show you what happens. 29 00:01:17,460 --> 00:01:22,110 Insert into cat's name and then values. 30 00:01:22,110 --> 00:01:24,720 And let's insert a cat called Todd. 31 00:01:25,590 --> 00:01:27,270 Okay, I hit enter. 32 00:01:27,300 --> 00:01:28,580 No problem whatsoever. 33 00:01:28,590 --> 00:01:30,660 Let's do a select start from cats. 34 00:01:30,660 --> 00:01:31,810 And what do we see? 35 00:01:31,830 --> 00:01:33,750 Todd is the name. 36 00:01:33,900 --> 00:01:35,280 That's what we expect. 37 00:01:35,400 --> 00:01:37,320 H is null. 38 00:01:37,410 --> 00:01:39,120 So remember, it doesn't mean zero. 39 00:01:39,120 --> 00:01:41,070 It means no value at all. 40 00:01:41,070 --> 00:01:42,270 It means unknown. 41 00:01:42,270 --> 00:01:45,930 There is no information here for age for this row. 42 00:01:46,320 --> 00:01:48,660 And maybe sometimes I want that. 43 00:01:48,990 --> 00:01:56,130 It depends on the situation because we might have different columns that aren't always required that 44 00:01:56,130 --> 00:01:57,210 could be empty. 45 00:01:57,210 --> 00:02:03,180 But a lot of the time we want to require values to be present in a column, meaning you cannot have 46 00:02:03,180 --> 00:02:03,690 null. 47 00:02:04,560 --> 00:02:07,260 Just as a side note right now, we could do something like this. 48 00:02:07,260 --> 00:02:12,660 I can insert a completely empty cat insert into cats no values. 49 00:02:12,990 --> 00:02:14,190 It's just like this. 50 00:02:14,820 --> 00:02:21,900 I select star from cats and now we've got this null null cat, empty name, empty age. 51 00:02:21,900 --> 00:02:24,570 It's still a valid row, but it doesn't. 52 00:02:24,570 --> 00:02:25,500 Shouldn't be valid. 53 00:02:25,500 --> 00:02:28,740 I don't think we would want our cats table to permit this. 54 00:02:28,740 --> 00:02:32,820 So this is where we can add a constraint in called not null. 55 00:02:32,940 --> 00:02:35,100 Now we could alter the table. 56 00:02:35,100 --> 00:02:36,060 We'll learn how to do that. 57 00:02:36,060 --> 00:02:38,730 But for now what we're going to do is make a new table. 58 00:02:38,730 --> 00:02:43,650 I'll just call it Cats two Very simple naming pattern here. 59 00:02:43,650 --> 00:02:47,580 It's simple enough, but in the real world, you probably wouldn't want to have cats. 60 00:02:47,580 --> 00:02:52,650 One cats, two cats, three cats for you probably just want a cats table that you are fixing and updating, 61 00:02:52,650 --> 00:02:54,150 but we don't know how to do that yet. 62 00:02:54,150 --> 00:02:57,750 So all we do is add on not null. 63 00:02:57,750 --> 00:03:04,770 When we are creating a table for each column, we specify the name of the column like age, that type 64 00:03:04,770 --> 00:03:09,030 int and then not null, just like we could do Primary key. 65 00:03:09,030 --> 00:03:13,500 That's another sort of add on for a column, not null as a constraint we can add. 66 00:03:13,590 --> 00:03:14,640 So let's try it. 67 00:03:14,910 --> 00:03:19,530 Let's make a new table create table cats to. 68 00:03:20,220 --> 00:03:21,500 And I'll open it up here. 69 00:03:21,510 --> 00:03:23,760 Oh, no, I misspelled create. 70 00:03:23,940 --> 00:03:24,900 Let's just close that. 71 00:03:24,900 --> 00:03:25,580 We'll get an error. 72 00:03:25,590 --> 00:03:26,360 That's OC. 73 00:03:26,400 --> 00:03:32,430 Create table catch two and then we're going to have instead of here. 74 00:03:32,850 --> 00:03:36,270 Let's go with name is a var car. 75 00:03:36,300 --> 00:03:37,860 Let's limit it to 50 again. 76 00:03:37,860 --> 00:03:42,330 Not know age is an int also not null. 77 00:03:42,480 --> 00:03:54,030 Close the n semicolon and now if we try to insert into cat two, let's just do name only and then values. 78 00:03:54,660 --> 00:04:04,290 Let's insert Bilbo into cats two we get an error field age doesn't have a default value, so that error 79 00:04:04,290 --> 00:04:08,460 might seem like a weird error you might not expect. 80 00:04:08,460 --> 00:04:14,760 Maybe you were expecting something like age must be present or an age cannot be null, but instead it 81 00:04:14,760 --> 00:04:16,649 says age doesn't have a default value. 82 00:04:16,680 --> 00:04:19,079 We'll learn how to set default values in a moment. 83 00:04:19,079 --> 00:04:24,330 But for now, if we take a look at Cats two, it's totally empty. 84 00:04:24,390 --> 00:04:25,890 So it did not work. 85 00:04:25,890 --> 00:04:35,280 Now, if I recall this and I did name and age and I say that Bilbo is, I don't know, 19 and then I 86 00:04:35,310 --> 00:04:42,360 select everything from cats to it worked so we can insert as normal unless we leave off one of those 87 00:04:42,360 --> 00:04:44,730 required fields or columns. 88 00:04:44,730 --> 00:04:49,980 So if I tried it the other way, right, if I didn't provide a name and I only provided an age, same 89 00:04:49,980 --> 00:04:50,520 thing. 90 00:04:50,520 --> 00:04:54,480 We get this error saying field name doesn't have a default value. 91 00:04:54,990 --> 00:05:01,140 Okay, so that's how we can add that constraint that says this column cannot be null, it must be present. 92 00:05:01,140 --> 00:05:05,340 And that's something we often want to enforce on important pieces of data. 93 00:05:05,340 --> 00:05:09,960 But not every single column needs to have not null, but a lot of them do. 94 00:05:09,960 --> 00:05:13,950 Oh, and the last thing I'll show you is that if we try and describe one of these tables again that 95 00:05:13,950 --> 00:05:19,290 has not null set up, we'll see NULL set to know for these different columns. 96 00:05:19,290 --> 00:05:21,690 So let's do describe cats. 97 00:05:21,690 --> 00:05:26,340 We see null as yes, cats to null is no. 98 00:05:26,340 --> 00:05:30,420 And in this case it's just both columns have no or not null. 99 00:05:30,420 --> 00:05:36,330 Both cases allow null, but obviously we don't have to always have the same value for every single column. 100 00:05:36,600 --> 00:05:37,100 Okay. 101 00:05:37,230 --> 00:05:39,990 Next we're going to tackle this default over here.