1 00:00:00,210 --> 00:00:07,050 The last thing I'll show you around altar table is how we can add constraints and drop constraints after 2 00:00:07,050 --> 00:00:08,310 a table is created. 3 00:00:08,490 --> 00:00:11,800 So like I said, there's a lot more to alter table. 4 00:00:11,820 --> 00:00:17,160 I'm giving you the most important pieces renaming columns, dropping columns, adding columns, modifying 5 00:00:17,160 --> 00:00:19,770 columns, adding and dropping constraints. 6 00:00:19,770 --> 00:00:22,260 But there's other things that you could do. 7 00:00:22,590 --> 00:00:29,670 But if we find alter or rather add constraint right here, we have different options. 8 00:00:29,670 --> 00:00:36,510 If we're adding a check constraint versus adding something like primary key constraint, unique foreign 9 00:00:36,510 --> 00:00:36,690 key. 10 00:00:36,720 --> 00:00:41,250 Once we talk about that, but let's just do a simple add a check constraint. 11 00:00:41,460 --> 00:00:46,830 So remember, this here means it's optional whenever we have those brackets. 12 00:00:47,040 --> 00:00:52,340 What we can do is add a constraint, give it a name check expression. 13 00:00:52,350 --> 00:00:53,340 It looks like a lot. 14 00:00:53,340 --> 00:00:55,830 But let's let's do a simple example. 15 00:00:56,520 --> 00:00:59,220 Let's say that we have our house's table. 16 00:01:00,060 --> 00:01:02,880 Let's make sure that purchase price has to be positive. 17 00:01:02,880 --> 00:01:06,940 And I've already made that house's table select star from houses. 18 00:01:06,960 --> 00:01:07,290 Right. 19 00:01:07,290 --> 00:01:13,890 I have described houses and at the moment I could insert a negative purchase price, but if I add a 20 00:01:13,890 --> 00:01:16,920 constraint so I would do alter table. 21 00:01:17,790 --> 00:01:21,060 And then add constraint. 22 00:01:22,660 --> 00:01:26,590 Again, reference the syntax if we ever have a question constraint is actually optional. 23 00:01:26,590 --> 00:01:31,780 I could just say add check, but I'm going to give it a name, so I'm going to say add constraint and 24 00:01:31,780 --> 00:01:34,750 then positive. 25 00:01:35,350 --> 00:01:36,220 Price. 26 00:01:37,030 --> 00:01:38,260 Technically purchase price. 27 00:01:38,270 --> 00:01:39,520 So p price. 28 00:01:39,520 --> 00:01:40,090 Sure. 29 00:01:40,860 --> 00:01:41,720 Check. 30 00:01:41,730 --> 00:01:44,070 And then what is the actual check we're doing? 31 00:01:44,220 --> 00:01:48,330 This would look the same as if we were just doing this when we created the table. 32 00:01:48,330 --> 00:01:50,310 But I'm going to say purchase price. 33 00:01:50,430 --> 00:01:50,910 Oops. 34 00:01:50,910 --> 00:01:52,140 Didn't mean to delete that. 35 00:01:52,140 --> 00:01:56,450 Purchase price has to be greater than or equal to zero. 36 00:01:56,460 --> 00:01:59,700 I guess you could buy it for zero, but you can't buy it for negative dollars. 37 00:02:00,560 --> 00:02:02,960 Okay, so let's try running that. 38 00:02:05,740 --> 00:02:06,840 And what is? 39 00:02:06,920 --> 00:02:08,229 Do I have a syntax error somewhere? 40 00:02:08,259 --> 00:02:09,190 Let's verify. 41 00:02:09,759 --> 00:02:11,080 Alter table. 42 00:02:11,110 --> 00:02:12,500 Oh, I didn't say what table. 43 00:02:12,520 --> 00:02:13,060 Duh. 44 00:02:13,090 --> 00:02:14,620 I hope you saw that before me. 45 00:02:15,190 --> 00:02:16,880 If not, it's okay. 46 00:02:16,900 --> 00:02:17,800 I didn't see it. 47 00:02:18,580 --> 00:02:20,650 Oh, and it doesn't like my quotes either. 48 00:02:20,650 --> 00:02:21,520 There. 49 00:02:21,700 --> 00:02:24,010 Anyway, let's create it. 50 00:02:24,010 --> 00:02:24,790 Now. 51 00:02:24,820 --> 00:02:27,600 Let's describe houses again. 52 00:02:27,610 --> 00:02:29,410 We're not going to see the constraint here. 53 00:02:29,410 --> 00:02:38,440 But if I try and insert into House's purchase price, sale, price, we already know that purchase price 54 00:02:38,440 --> 00:02:40,060 has to be greater than the sale price. 55 00:02:40,060 --> 00:02:43,750 But let's say or other way around, sale price has to be greater than purchase. 56 00:02:43,750 --> 00:02:49,690 But let's say I purchased it for negative one dollars and then I sold it for $4. 57 00:02:49,960 --> 00:02:52,210 I get an error check constraint. 58 00:02:52,210 --> 00:02:54,310 Positive P price is violated. 59 00:02:54,310 --> 00:02:56,410 That is the constraint I just added. 60 00:02:56,740 --> 00:03:01,570 Now I can also drop a constraint using drop. 61 00:03:02,310 --> 00:03:03,120 Where are you? 62 00:03:03,150 --> 00:03:07,560 Drop and then check some constraint name. 63 00:03:07,650 --> 00:03:10,910 I could also drop constraint in the name of that constraint. 64 00:03:10,920 --> 00:03:12,630 In this case, both of them will work. 65 00:03:12,630 --> 00:03:21,120 So as long as I have the name positive P price, I can now do alter table houses. 66 00:03:21,950 --> 00:03:25,340 And then drop constraint. 67 00:03:26,880 --> 00:03:29,340 Positive P price. 68 00:03:30,720 --> 00:03:38,760 And if I now try and insert that what used to be invalid and negative purchase price, now it's fine. 69 00:03:38,880 --> 00:03:45,240 Now I still have the other constraint in there that says that sale price has to be greater than purchase 70 00:03:45,240 --> 00:03:45,660 price. 71 00:03:45,660 --> 00:03:48,320 So I could drop that one too if I wanted to. 72 00:03:48,570 --> 00:03:54,510 All I need to do is alter table house's drop constraint and then SE price GTP price. 73 00:03:54,510 --> 00:03:57,540 And that's the name I came up with when I added that constraint. 74 00:03:58,740 --> 00:04:01,110 So Ultra table is powerful. 75 00:04:01,110 --> 00:04:05,580 It can be overwhelming to remember how it works, but just go to the docs if you ever have questions. 76 00:04:05,580 --> 00:04:10,470 Remember if you see those square brackets there or whatever those stripe brackets, square brackets, 77 00:04:10,470 --> 00:04:12,210 that means something is optional.