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.

