1
00:00:00,150 --> 00:00:05,990
The next thing I'll show you is that we can set up our own constraints that use multiple columns.

2
00:00:06,000 --> 00:00:14,730
So this example here has a constraint that makes sure that the name and address combination are unique.

3
00:00:14,970 --> 00:00:19,740
So in the company's table, the name itself doesn't have to be unique because companies could have the

4
00:00:19,740 --> 00:00:25,560
same name, and the address doesn't have to be unique because there may be multiple companies housed

5
00:00:25,560 --> 00:00:29,910
in the same building, the same office, for example, or run from the same home.

6
00:00:30,090 --> 00:00:35,430
But the combination of the two is what needs to be unique name and address.

7
00:00:35,700 --> 00:00:37,490
So let me show you this.

8
00:00:37,500 --> 00:00:42,030
If I try running it and I think I'll tweak it to just make it simpler.

9
00:00:42,990 --> 00:00:48,570
So let's not even bother with an ID, Let's not bother with a phone number or primary key.

10
00:00:48,600 --> 00:00:51,900
Let's just do name and address, neither of which can be null.

11
00:00:52,050 --> 00:00:54,000
But they don't have to be unique on their own.

12
00:00:54,000 --> 00:00:56,430
They have to be unique as a combination.

13
00:00:57,930 --> 00:01:05,760
So let's create this table and then let's insert into companies name and address.

14
00:01:05,760 --> 00:01:08,550
And I'm going to keep names and addresses super simple.

15
00:01:08,580 --> 00:01:12,430
Our first name will be Blackbird Auto.

16
00:01:12,570 --> 00:01:13,680
Okay, sure.

17
00:01:14,250 --> 00:01:19,100
And then address will just be one, two, three Spruce Street OC.

18
00:01:19,530 --> 00:01:20,340
That works fine.

19
00:01:20,340 --> 00:01:21,210
No problem.

20
00:01:21,480 --> 00:01:26,040
Now, if I did the same thing, but this time maybe there's a different company located at one, two,

21
00:01:26,040 --> 00:01:30,720
three spruce called Luigi's Pies.

22
00:01:32,330 --> 00:01:33,170
Also fine.

23
00:01:33,170 --> 00:01:34,040
So one, two, three.

24
00:01:34,040 --> 00:01:36,950
Spruce is duplicated, but that doesn't matter.

25
00:01:37,280 --> 00:01:46,580
However, if I then have some business like Luigi's Pies at 1 to 3 spruce, which is already in there,

26
00:01:46,580 --> 00:01:51,800
so the same business name at the same location, we get an error duplicate entry.

27
00:01:51,800 --> 00:01:52,820
Luigi's Pies.

28
00:01:52,820 --> 00:01:56,420
One, two, three spruce for this key name address.

29
00:01:56,420 --> 00:01:59,870
So I could also give this a better name instead of just name address.

30
00:02:00,110 --> 00:02:03,850
I could do name, address, combo, unique name address, something like that.

31
00:02:03,860 --> 00:02:05,570
Now this is using unique.

32
00:02:05,570 --> 00:02:07,760
This is not a check constraint, right?

33
00:02:07,760 --> 00:02:10,910
This is using an existing constraint like unique.

34
00:02:11,570 --> 00:02:16,790
We can also do our own custom check constraints that use multiple columns.

35
00:02:16,790 --> 00:02:27,530
Like let's say that we flip houses so create table houses and we keep track of the price we bought a

36
00:02:27,530 --> 00:02:28,400
house for.

37
00:02:28,400 --> 00:02:35,120
So the purchase price is let's just make it an integer.

38
00:02:35,150 --> 00:02:39,410
I guess we could worry about sense as well, but let's just keep it as an integer.

39
00:02:39,410 --> 00:02:41,150
Just to make this short and simple.

40
00:02:41,420 --> 00:02:42,470
It can't be null.

41
00:02:43,310 --> 00:02:47,930
And then we'll have the sale price, which is also an integer, can't be null.

42
00:02:48,650 --> 00:02:53,960
And I can add my own constraint that says the sale price has to be greater than the purchase price.

43
00:02:54,290 --> 00:02:56,600
Technically, you could sell a house and lose money.

44
00:02:56,600 --> 00:02:57,890
It happens all the time.

45
00:02:57,890 --> 00:03:03,770
But let's just say for this example, our system only lets you set a sale price that is greater than

46
00:03:03,770 --> 00:03:06,110
or equal to what you purchased it for.

47
00:03:06,440 --> 00:03:09,020
So this is a multi column check constraint.

48
00:03:09,020 --> 00:03:16,490
I would do constraint, come up with some name like I don't know how about just purchase price or rather

49
00:03:16,490 --> 00:03:19,430
sale price greater than purchase price.

50
00:03:19,430 --> 00:03:21,920
I mean that is confusing to look at.

51
00:03:21,920 --> 00:03:25,700
But just for the sake of brevity, we'll start with that.

52
00:03:25,910 --> 00:03:29,510
Or maybe se price greater than p price.

53
00:03:30,590 --> 00:03:31,160
Sure.

54
00:03:31,370 --> 00:03:33,290
And then we write check.

55
00:03:34,010 --> 00:03:35,210
And then what are we checking?

56
00:03:35,210 --> 00:03:42,170
We're checking to see that purchase price is less than or equal to sale price.

57
00:03:42,170 --> 00:03:48,380
Although to be consistent, I would reverse that and say sale price has to be greater than or equal

58
00:03:48,380 --> 00:03:52,280
to purchase price only because that's the name of this constraint.

59
00:03:52,370 --> 00:03:55,520
I just think it makes more logical sense to keep that in the same order.

60
00:03:56,440 --> 00:03:59,860
Okay, so let's make this table.

61
00:04:00,820 --> 00:04:13,990
And then if I insert into houses, purchase price first, sell price, second values, and let's do

62
00:04:13,990 --> 00:04:15,490
really simple prices.

63
00:04:15,490 --> 00:04:19,660
Like we bought the house for $100, we sold it for $200.

64
00:04:19,690 --> 00:04:20,890
Great profit.

65
00:04:21,310 --> 00:04:22,990
Okay, that works.

66
00:04:22,990 --> 00:04:30,310
But if we bought the house for five or $300 and we sold it for $250, oh no.

67
00:04:30,310 --> 00:04:35,620
We get an error check constraint sale price greater than purchase price is violated.

68
00:04:35,770 --> 00:04:41,260
So just another example of a check constraint in this example used two columns and you're not limited

69
00:04:41,260 --> 00:04:45,850
to simple comparisons like greater than, although that is what is probably most common.

70
00:04:45,850 --> 00:04:48,280
Comparing equality greater than less than.

71
00:04:48,280 --> 00:04:55,330
But as we saw, you can do things like use string functions and date functions and time functions and

72
00:04:55,330 --> 00:04:56,020
all of that.

73
00:04:56,020 --> 00:05:02,320
So you could write a constraint to see that a birthday came before some data, after some date, or

74
00:05:02,320 --> 00:05:09,910
that a time is between one, I don't know, noon in five or it's during the workday, it's between 8

75
00:05:09,910 --> 00:05:12,070
a.m. and five or whatever your workday is.

76
00:05:12,070 --> 00:05:14,710
So anyway, you can write constraints, give them names.

77
00:05:14,710 --> 00:05:15,730
It's pretty powerful.

78
00:05:15,850 --> 00:05:17,380
That's it for constraints.

79
00:05:17,380 --> 00:05:20,200
Next up, we're going to move on to Alter Table.