1
00:00:00,150 --> 00:00:01,310
Welcome back, everyone.

2
00:00:01,320 --> 00:00:06,570
In this section, we're going to learn about more constraints that we can use when setting up a table.

3
00:00:06,570 --> 00:00:11,400
So when I say constraints, I'm referring to things like not null, right?

4
00:00:11,400 --> 00:00:12,660
We saw what that meant.

5
00:00:12,660 --> 00:00:16,200
If we use not null on a column, it can't be null.

6
00:00:16,230 --> 00:00:17,580
That is a constraint.

7
00:00:17,580 --> 00:00:21,180
We also saw that we could use primary key.

8
00:00:21,180 --> 00:00:22,440
There's another example.

9
00:00:22,440 --> 00:00:25,950
And the next one of those constraints we're going to learn is called unique.

10
00:00:25,950 --> 00:00:27,630
And it's exactly what it sounds like.

11
00:00:27,630 --> 00:00:33,510
It's a way of setting up and enforcing that uniqueness of a particular column.

12
00:00:33,840 --> 00:00:39,360
So in this table companies, there's a lot of stuff going on, but all that really matters is right

13
00:00:39,360 --> 00:00:39,900
here.

14
00:00:39,930 --> 00:00:44,310
Phone is a bar chart, 15 characters at most.

15
00:00:44,310 --> 00:00:49,200
It's not null, it cannot be null and it has to be unique.

16
00:00:49,860 --> 00:00:54,960
So let me show you what happens if we set up a table with this unique constraint.

17
00:00:54,960 --> 00:00:56,130
So let's do a simpler one.

18
00:00:56,130 --> 00:00:59,210
Just create table contacts.

19
00:00:59,220 --> 00:01:02,820
So we'll have a contact table and it's going to be super simple.

20
00:01:02,820 --> 00:01:11,250
We'll just have a name which is var char, not null, and let's have that be, I don't know, 100 characters.

21
00:01:11,730 --> 00:01:16,170
And then we'll also have a phone, which is for Char.

22
00:01:16,170 --> 00:01:21,600
Sure, we'll say 15 and we'll say it also can't be null and it has to be unique.

23
00:01:22,170 --> 00:01:28,410
Let's try running this OC and I'm going to do an insert into contacts.

24
00:01:30,290 --> 00:01:34,390
Well do name and phone values.

25
00:01:34,400 --> 00:01:35,960
And let's start with a simple one.

26
00:01:35,960 --> 00:01:40,070
Like, I don't know, Billy Bob is our contact name.

27
00:01:40,070 --> 00:01:46,910
Phone number is 87812134, five, five.

28
00:01:47,810 --> 00:01:52,490
So that's our first person that we will insert or our first contact.

29
00:01:52,490 --> 00:01:54,890
Let's just make sure that works at my semicolon.

30
00:01:56,010 --> 00:01:57,140
Seems to have worked.

31
00:01:57,170 --> 00:02:04,130
Of course we'll do select star from contacts and nothing is at all changed or unexpected.

32
00:02:04,160 --> 00:02:08,750
However, if I now go and try and insert a couple more, let's insert into contacts.

33
00:02:09,320 --> 00:02:10,370
We can have the same name.

34
00:02:10,370 --> 00:02:10,940
Billy Bob.

35
00:02:10,940 --> 00:02:12,830
It doesn't matter, but let's do another name.

36
00:02:12,830 --> 00:02:17,470
How about like Timmy, Tim, Tim.

37
00:02:17,480 --> 00:02:21,770
And then we keep the same phone number and I insert this.

38
00:02:22,070 --> 00:02:23,030
Oh, no.

39
00:02:23,030 --> 00:02:25,460
We get an error says duplicate entry.

40
00:02:25,490 --> 00:02:26,050
Eight, seven, eight.

41
00:02:26,060 --> 00:02:29,660
One, two, one, three, four, five, five for this key phone.

42
00:02:29,960 --> 00:02:33,140
So it's maybe not the most helpful message.

43
00:02:33,140 --> 00:02:34,840
I mean, it's clear enough what's going on.

44
00:02:34,850 --> 00:02:38,360
It says there's a duplicate value and that's fine.

45
00:02:38,630 --> 00:02:44,300
It's not really this it's not like an end user facing error that says that username or that phone number

46
00:02:44,300 --> 00:02:46,070
is already taken or something like that.

47
00:02:46,070 --> 00:02:50,810
Instead, it's just nitty gritty duplicate entry for this key.

48
00:02:50,900 --> 00:02:52,580
But it works.

49
00:02:52,580 --> 00:02:59,960
If I select Star from Contacts, we'll see that we only have one person in there, one contact with

50
00:02:59,960 --> 00:03:00,860
that phone number.

51
00:03:01,070 --> 00:03:05,360
So Billy Bob, like I said, that can be duplicated.

52
00:03:05,570 --> 00:03:12,230
Billy Bob But if I change this just by one digit, let's say that's the phone number and I enter that.

53
00:03:12,230 --> 00:03:13,220
No problem.

54
00:03:13,220 --> 00:03:17,030
Select star from contact's name.

55
00:03:17,030 --> 00:03:18,080
It's Billy Bob twice.

56
00:03:18,080 --> 00:03:20,390
But phone has to be unique.

57
00:03:21,260 --> 00:03:22,880
So that's the unique constraint.

58
00:03:22,880 --> 00:03:24,800
We can add it on to any of the fields.

59
00:03:24,800 --> 00:03:26,120
We want to define.

60
00:03:26,630 --> 00:03:30,230
Something like Primary key, as we already know, has to be unique.

61
00:03:30,230 --> 00:03:34,820
So you don't want to say primary key and unique for the same column in the same way.

62
00:03:34,820 --> 00:03:41,270
That not null is redundant for a primary key because primary keys are required, they can't be null

63
00:03:41,270 --> 00:03:42,890
and they must be unique.

64
00:03:43,250 --> 00:03:43,780
Okay.