Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:01,420 --> 00:00:03,040
In this lesson, we'll
be taking a look
2
00:00:03,040 --> 00:00:05,770
at primary and unique keys.
3
00:00:05,770 --> 00:00:09,010
So primary keys are
commonly associated
4
00:00:09,010 --> 00:00:11,230
with the term, data integrity.
5
00:00:11,230 --> 00:00:13,810
And data integrity
is the process
6
00:00:13,810 --> 00:00:18,610
of ensuring that data conforms
to a particular business model.
7
00:00:18,610 --> 00:00:21,610
In order to achieve
data integrity,
8
00:00:21,610 --> 00:00:24,550
we use what's known as
a constraint or database
9
00:00:24,550 --> 00:00:25,720
constraint.
10
00:00:25,720 --> 00:00:28,330
And a constraint is a
database object that
11
00:00:28,330 --> 00:00:30,910
enforces that data integrity.
12
00:00:30,910 --> 00:00:33,790
We can build some of our
business models, some
13
00:00:33,790 --> 00:00:37,180
of our business rules
into the database
14
00:00:37,180 --> 00:00:38,560
in the form of constraints.
15
00:00:38,560 --> 00:00:42,430
And those constraints help
us to achieve data integrity.
16
00:00:42,430 --> 00:00:45,220
So the primary key/foreign
key relationship
17
00:00:45,220 --> 00:00:50,080
is really the core of the RDBMS
because it allows two tables
18
00:00:50,080 --> 00:00:52,510
to have a relationship
between each other.
19
00:00:52,510 --> 00:00:54,880
And that's what an
RDBMS is all about.
20
00:00:54,880 --> 00:00:57,190
The idea that we can
separate the data out
21
00:00:57,190 --> 00:01:01,150
into tables as opposed to the
old flat file model but still
22
00:01:01,150 --> 00:01:03,130
have them be related.
23
00:01:03,130 --> 00:01:06,190
And so the primary key/foreign
key relationship is really
24
00:01:06,190 --> 00:01:09,760
we could think of as the
parent/child data relationship
25
00:01:09,760 --> 00:01:12,610
where the parent is
the primary key value
26
00:01:12,610 --> 00:01:15,290
and the child is the
foreign key value.
27
00:01:15,290 --> 00:01:17,950
So a primary key has
certain characteristics
28
00:01:17,950 --> 00:01:19,660
that it has to conform to.
29
00:01:19,660 --> 00:01:22,250
First and foremost,
it cannot be null.
30
00:01:22,250 --> 00:01:25,930
So a primary key
cannot be a null value.
31
00:01:25,930 --> 00:01:27,460
And it must be unique.
32
00:01:27,460 --> 00:01:30,980
It cannot have any
duplicates of a primary key.
33
00:01:30,980 --> 00:01:33,290
So when we talk
about a primary key,
34
00:01:33,290 --> 00:01:37,570
we use the phrase that a
primary key uniquely identifies
35
00:01:37,570 --> 00:01:40,060
every row in a table.
36
00:01:40,060 --> 00:01:42,690
So any primary key
value will be associated
37
00:01:42,690 --> 00:01:44,610
with one and only one row.
38
00:01:44,610 --> 00:01:48,450
Furthermore, a primary key can
be paired with a foreign key,
39
00:01:48,450 --> 00:01:50,250
but it doesn't have to be.
40
00:01:50,250 --> 00:01:53,790
It's not uncommon to
see data models where
41
00:01:53,790 --> 00:01:58,230
tables have primary keys but not
necessarily have foreign keys.
42
00:01:58,230 --> 00:01:59,670
Although without
the foreign key,
43
00:01:59,670 --> 00:02:03,840
we're not enforcing the business
model and the business rules.
44
00:02:03,840 --> 00:02:06,030
There are coding standards
in some companies
45
00:02:06,030 --> 00:02:10,170
that just say simply that every
table must have a primary key.
46
00:02:10,170 --> 00:02:14,010
So they may use a synthetic
key with a sequence, something
47
00:02:14,010 --> 00:02:18,060
of that nature, but a
primary key on every table
48
00:02:18,060 --> 00:02:21,810
so that that value can uniquely
identify every individual row
49
00:02:21,810 --> 00:02:22,950
in the table.
50
00:02:22,950 --> 00:02:25,310
A primary key can
also be composites.
51
00:02:25,310 --> 00:02:27,750
So it can be a composite
like a composite index.
52
00:02:27,750 --> 00:02:32,250
That is to say that it includes
more than one column in it.
53
00:02:32,250 --> 00:02:35,980
So we think of a primary key
column, and that's very common.
54
00:02:35,980 --> 00:02:40,560
But it can also be a composite
with two or more even columns.
55
00:02:40,560 --> 00:02:42,840
And a unique key
is exactly the same
56
00:02:42,840 --> 00:02:45,060
as a primary key,
the same rules,
57
00:02:45,060 --> 00:02:48,250
except that null
values are allowed.
58
00:02:48,250 --> 00:02:50,880
So the question comes
up every now and then,
59
00:02:50,880 --> 00:02:54,840
can a unique key be
paired with a foreign key
60
00:02:54,840 --> 00:02:56,650
to enforce data integrity?
61
00:02:56,650 --> 00:02:58,050
And yes it can.
62
00:02:58,050 --> 00:03:01,980
Although it's not always the
best choice because it does
63
00:03:01,980 --> 00:03:03,450
allow null values.
64
00:03:03,450 --> 00:03:05,190
So those unique
key values do not
65
00:03:05,190 --> 00:03:09,330
match any foreign key
values if they are null.
66
00:03:09,330 --> 00:03:11,400
We can create primary
and unique keys
67
00:03:11,400 --> 00:03:13,480
a number of different ways.
68
00:03:13,480 --> 00:03:17,310
So we can create them in line
which is within the column
69
00:03:17,310 --> 00:03:19,360
definition themselves.
70
00:03:19,360 --> 00:03:22,560
So when we use the
create table statement
71
00:03:22,560 --> 00:03:25,350
and we define the columns
and their data types,
72
00:03:25,350 --> 00:03:29,290
we can also define that
column as being a primary key.
73
00:03:29,290 --> 00:03:31,660
We can do what's
called out-of-line,
74
00:03:31,660 --> 00:03:34,410
which is at the end of a
create table statement.
75
00:03:34,410 --> 00:03:37,290
So the entire create table
statement is written.
76
00:03:37,290 --> 00:03:39,000
And then at the
end of it, there's
77
00:03:39,000 --> 00:03:42,330
a clause that allows the
creation of a primary key.
78
00:03:42,330 --> 00:03:45,060
And it allows us to give
the primary key value a name
79
00:03:45,060 --> 00:03:46,510
and so forth.
80
00:03:46,510 --> 00:03:50,220
And finally, we can do it after
creation using the altar table
81
00:03:50,220 --> 00:03:50,730
command.
82
00:03:50,730 --> 00:03:54,420
So we create the table without
a primary key or unique key.
83
00:03:54,420 --> 00:03:56,610
And then we issue
another statement
84
00:03:56,610 --> 00:03:58,950
after using altar table.
85
00:03:58,950 --> 00:04:02,530
So which way that these
are done in an organization
86
00:04:02,530 --> 00:04:05,050
usually depends on
their coding standards.
87
00:04:05,050 --> 00:04:07,440
Let's say that there
is a script to create
88
00:04:07,440 --> 00:04:09,810
a number of different tables.
89
00:04:09,810 --> 00:04:13,590
Under this model, you would
have the create table statements
90
00:04:13,590 --> 00:04:16,920
that run, and then they
would just create the table
91
00:04:16,920 --> 00:04:18,060
with its columns.
92
00:04:18,060 --> 00:04:20,400
After that, there
would be an altar table
93
00:04:20,400 --> 00:04:23,500
that added all of the
individual constraints,
94
00:04:23,500 --> 00:04:26,760
including primary keys, foreign
keys, or check constraints,
95
00:04:26,760 --> 00:04:28,710
or whatever it would be.
96
00:04:28,710 --> 00:04:31,440
That's one model, but not
every coding standard model
97
00:04:31,440 --> 00:04:32,980
is the same.
98
00:04:32,980 --> 00:04:34,700
So let's take a look
at how this works.
99
00:04:37,330 --> 00:04:40,090
Connect with our
Scott user, and we're
100
00:04:40,090 --> 00:04:44,730
going to create a table
with an inline constraint.
101
00:04:44,730 --> 00:04:50,360
I call this doctor
il, doctor id column.
102
00:04:50,360 --> 00:04:53,650
And here we put the
key word primary key.
103
00:04:53,650 --> 00:04:56,310
So we're defining doctor
id as the primary key.
104
00:05:02,670 --> 00:05:05,040
And then execute the statement.
105
00:05:05,040 --> 00:05:10,050
And so the doctor il table
is created with the doctor id
106
00:05:10,050 --> 00:05:11,430
as the primary key.
107
00:05:11,430 --> 00:05:14,080
There's no foreign keys
associated to it at this point.
108
00:05:14,080 --> 00:05:16,590
But it is structured
as the primary key.
109
00:05:16,590 --> 00:05:20,500
So that's one way of adding
a primary key in line.
110
00:05:20,500 --> 00:05:23,640
So let's look at an out-of-line.
111
00:05:23,640 --> 00:05:25,170
I'm going to copy most of this.
112
00:05:27,950 --> 00:05:30,580
I'm going to put a comma here.
113
00:05:30,580 --> 00:05:32,830
So up to this point,
this is a creation
114
00:05:32,830 --> 00:05:36,400
of a table without
a primary key.
115
00:05:36,400 --> 00:05:40,150
So here at the end
out-of-line I put primary key.
116
00:05:40,150 --> 00:05:45,150
And I define the column
for the primary key.
117
00:05:45,150 --> 00:05:47,940
So I'm creating the
same structured table
118
00:05:47,940 --> 00:05:49,470
only I'm doing it out-of-line.
119
00:05:49,470 --> 00:05:51,220
It's a different name as well.
120
00:05:51,220 --> 00:05:53,480
So let's execute that.
121
00:05:53,480 --> 00:05:56,450
So that's an
out-of-line constraint.
122
00:05:56,450 --> 00:06:00,950
Finally, the primary key could
be done after the create table
123
00:06:00,950 --> 00:06:02,970
statement.
124
00:06:02,970 --> 00:06:04,820
Say, ac for after creation.
125
00:06:07,720 --> 00:06:11,590
So this is just creating
the table itself.
126
00:06:11,590 --> 00:06:19,170
And then we use an altar table,
doctor ac, add primary key.
127
00:06:19,170 --> 00:06:23,680
And of course, we have
to tell it the column.
128
00:06:23,680 --> 00:06:29,200
So now the doctor ac table has
had a primary key added to it.
129
00:06:29,200 --> 00:06:31,810
Now if there's already
data in the table,
130
00:06:31,810 --> 00:06:35,140
keep in mind that the
addition of the primary key
131
00:06:35,140 --> 00:06:36,850
is going to validate the data.
132
00:06:36,850 --> 00:06:39,400
So it's going to go
through and make sure
133
00:06:39,400 --> 00:06:42,700
that all the rules of a
primary key are conformed to.
134
00:06:42,700 --> 00:06:44,070
It has no null values.
135
00:06:44,070 --> 00:06:47,290
And it has all distinct
values, no duplicates.
136
00:06:47,290 --> 00:06:49,120
But those are three
different ways
137
00:06:49,120 --> 00:06:52,120
to add a primary key in a table.
11009
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.