Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:01,410 --> 00:00:02,910
In this lesson,
we'll take a look
2
00:00:02,910 --> 00:00:05,640
at sequences in a database.
3
00:00:05,640 --> 00:00:08,380
So to begin our
discussion of sequences,
4
00:00:08,380 --> 00:00:10,560
let's talk about a
couple of terms here,
5
00:00:10,560 --> 00:00:14,340
natural keys versus
synthetic keys.
6
00:00:14,340 --> 00:00:18,510
So a natural key is a value
in a database or column
7
00:00:18,510 --> 00:00:22,950
value that actually uses
the true data that's
8
00:00:22,950 --> 00:00:25,630
being stored in the database.
9
00:00:25,630 --> 00:00:28,710
So if we have an
employee table, and we
10
00:00:28,710 --> 00:00:31,680
have a column for
social security number,
11
00:00:31,680 --> 00:00:34,380
we could use that
as a primary key.
12
00:00:34,380 --> 00:00:38,520
Because we make the assumption
that value for social security
13
00:00:38,520 --> 00:00:42,450
number would conform to
the rules of a primary key,
14
00:00:42,450 --> 00:00:44,760
mainly, that there are
no duplicates allowed.
15
00:00:44,760 --> 00:00:47,860
So there's no duplicate
social security numbers.
16
00:00:47,860 --> 00:00:51,090
A synthetic key is a key
value that we add, say,
17
00:00:51,090 --> 00:00:54,870
for a primary key that
is completely generated.
18
00:00:54,870 --> 00:00:55,980
So it's synthetic.
19
00:00:55,980 --> 00:01:00,390
It has no relationship to the
data itself in any real way,
20
00:01:00,390 --> 00:01:04,300
except to act as a
primary key value.
21
00:01:04,300 --> 00:01:07,980
So there's pros and cons for
both ways when you do this.
22
00:01:07,980 --> 00:01:11,850
But it's really important, in
my opinion, to choose a method
23
00:01:11,850 --> 00:01:13,240
and stick with it.
24
00:01:13,240 --> 00:01:17,250
So natural keys have the
benefit of using less space,
25
00:01:17,250 --> 00:01:19,770
and they don't require
an additional column.
26
00:01:19,770 --> 00:01:23,310
It's only the space that would
be used in a synthetic key, so
27
00:01:23,310 --> 00:01:25,290
that key value,
or the key values
28
00:01:25,290 --> 00:01:27,630
throughout all the
tables in the database.
29
00:01:27,630 --> 00:01:30,510
But there is some savings
there, and also you
30
00:01:30,510 --> 00:01:32,460
don't have to have
that extra column.
31
00:01:32,460 --> 00:01:36,690
Synthetic keys, they're going
to actually abstract the values
32
00:01:36,690 --> 00:01:38,590
from the actual data.
33
00:01:38,590 --> 00:01:40,830
So to give you an example
of a coding standard I've
34
00:01:40,830 --> 00:01:45,870
seen in the past is you have
a data model with many tables,
35
00:01:45,870 --> 00:01:49,140
and every table has
to have a primary key.
36
00:01:49,140 --> 00:01:51,840
In many cases,
those primary keys
37
00:01:51,840 --> 00:01:54,540
can't be established
from the data that's
38
00:01:54,540 --> 00:01:55,770
in those tables themselves.
39
00:01:55,770 --> 00:01:57,990
There may not be
any column value
40
00:01:57,990 --> 00:02:02,430
that can be used to actually
make a primary key value
41
00:02:02,430 --> 00:02:05,700
or to make a value
that allows that value
42
00:02:05,700 --> 00:02:10,140
to distinctly identify that
row from all of the other rows.
43
00:02:10,140 --> 00:02:12,810
And so, since the rules
were that a table must
44
00:02:12,810 --> 00:02:16,290
have our primary key,
every one of the tables
45
00:02:16,290 --> 00:02:19,260
had a synthetic key
for the primary key.
46
00:02:19,260 --> 00:02:22,890
So a sequential number,
1, 2, 3, on and on,
47
00:02:22,890 --> 00:02:25,510
was generated for that table.
48
00:02:25,510 --> 00:02:28,780
And then that table had
the primary key values
49
00:02:28,780 --> 00:02:31,200
that were actually
separate from the data.
50
00:02:31,200 --> 00:02:32,910
They didn't have
any relationship.
51
00:02:32,910 --> 00:02:34,770
But they could
function in the way
52
00:02:34,770 --> 00:02:37,050
that a relational
table needed to.
53
00:02:37,050 --> 00:02:40,620
In my opinion, synthetic
keys are beneficial,
54
00:02:40,620 --> 00:02:43,560
because there are so many
problems in data models
55
00:02:43,560 --> 00:02:47,100
trying to find a column or
columns that can uniquely
56
00:02:47,100 --> 00:02:49,600
identify every row in a table.
57
00:02:49,600 --> 00:02:52,320
So if we choose to
use synthetic keys,
58
00:02:52,320 --> 00:02:54,790
the sequence can
be very important.
59
00:02:54,790 --> 00:02:57,750
So a sequence is a
database object that
60
00:02:57,750 --> 00:02:59,940
generates sequential numbers.
61
00:02:59,940 --> 00:03:01,860
And that's really all it does.
62
00:03:01,860 --> 00:03:05,010
Now, of course, if we decided
to use synthetic keys,
63
00:03:05,010 --> 00:03:07,440
we could have our
code constructed
64
00:03:07,440 --> 00:03:10,800
to go out and search for
a value, the greatest
65
00:03:10,800 --> 00:03:15,370
value in the table, increment by
one, and then insert that row.
66
00:03:15,370 --> 00:03:17,190
But a sequence
keeps us from having
67
00:03:17,190 --> 00:03:21,300
to do that, because we can just
reference the sequence itself,
68
00:03:21,300 --> 00:03:24,450
and say, give me the
next available number
69
00:03:24,450 --> 00:03:25,750
in the sequence.
70
00:03:25,750 --> 00:03:30,360
And so we use a couple of
modifiers, NEXTVAL and CURRVAL
71
00:03:30,360 --> 00:03:33,250
in order to advance
our sequences.
72
00:03:33,250 --> 00:03:36,690
So this is much easier to
show than it is to explain.
73
00:03:36,690 --> 00:03:40,020
So let's take a look
at what we have here.
74
00:03:40,020 --> 00:03:43,200
Let's connect to our
Scott user, and we're
75
00:03:43,200 --> 00:03:50,320
going to do select star from
salgrade order by grade.
76
00:03:50,320 --> 00:03:54,160
So we have this grade column,
which is incrementing numbers.
77
00:03:54,160 --> 00:03:55,510
So these are salary grades.
78
00:03:55,510 --> 00:03:59,860
So grade 1 has a low sal of
700 and a high sal of 1,200,
79
00:03:59,860 --> 00:04:01,360
and so on, and so forth.
80
00:04:01,360 --> 00:04:04,240
So if we wanted to add
more salary grades,
81
00:04:04,240 --> 00:04:06,580
we need to make
sure that grade--
82
00:04:06,580 --> 00:04:09,250
if we're going to use
grade is the primary key--
83
00:04:09,250 --> 00:04:10,560
is never a duplicate.
84
00:04:10,560 --> 00:04:13,870
So we can't have another grade
5 with different high sal
85
00:04:13,870 --> 00:04:15,990
and low sal.
86
00:04:15,990 --> 00:04:18,510
Now again, we could
go out in our code,
87
00:04:18,510 --> 00:04:21,750
and we could select the max
value for grade, increment
88
00:04:21,750 --> 00:04:23,850
by one, and then do the insert.
89
00:04:23,850 --> 00:04:26,350
Or we could use a sequence.
90
00:04:26,350 --> 00:04:32,840
So let's say, select
max(grade) from salgrade.
91
00:04:32,840 --> 00:04:34,930
Now we can see it.
92
00:04:34,930 --> 00:04:36,610
But just to
establish this, let's
93
00:04:36,610 --> 00:04:39,490
say, OK, that's the
maximum value for the grade
94
00:04:39,490 --> 00:04:41,530
column in salgrade.
95
00:04:41,530 --> 00:04:44,140
Now I'm going to attempt
to create a sequence.
96
00:04:47,680 --> 00:04:54,300
Salgrade_seq, and we
put INCREMENT by 1,
97
00:04:54,300 --> 00:04:57,060
and start with 6.
98
00:04:57,060 --> 00:04:58,470
Why do we start with 6?
99
00:04:58,470 --> 00:05:01,950
Well, because our max
value right now is 5.
100
00:05:01,950 --> 00:05:05,340
So we're going to use a sequence
to insert data into salgrade
101
00:05:05,340 --> 00:05:06,660
from now on.
102
00:05:06,660 --> 00:05:08,610
And so, that's
what we want to do.
103
00:05:08,610 --> 00:05:10,590
We have the sequence name.
104
00:05:10,590 --> 00:05:12,780
Increment by whatever
value we want.
105
00:05:12,780 --> 00:05:14,790
In this case, we'll
increment by 1.
106
00:05:14,790 --> 00:05:17,730
You can increment by 2,
or 5, or 10, or whatever.
107
00:05:17,730 --> 00:05:20,680
And then we have
the starting value.
108
00:05:20,680 --> 00:05:25,450
So let's attempt to create this,
and the sequence is created.
109
00:05:25,450 --> 00:05:27,280
So how do we use a sequence?
110
00:05:27,280 --> 00:05:28,800
How do we reference it?
111
00:05:28,800 --> 00:05:31,860
Well, let's say we have
a new salary grade to put
112
00:05:31,860 --> 00:05:34,050
into the salgrade table.
113
00:05:34,050 --> 00:05:40,270
So we could say, insert
into_salgrade values.
114
00:05:40,270 --> 00:05:43,060
And instead of putting
a literal value here,
115
00:05:43,060 --> 00:05:48,190
we're going to use the
sequencer name, salgrade_seq,
116
00:05:48,190 --> 00:05:49,980
and the modifier nextval.
117
00:05:53,000 --> 00:05:59,460
And then we'll put our high sal
and low sal to be consistent.
118
00:05:59,460 --> 00:06:01,710
The row's inserted.
119
00:06:01,710 --> 00:06:07,350
Now we select, and we see
that we have now grade 6
120
00:06:07,350 --> 00:06:10,140
with the low sal and the
high sal that we requested.
121
00:06:10,140 --> 00:06:14,280
So we didn't put a literal
6 into the insert statement.
122
00:06:14,280 --> 00:06:16,320
We just referenced the sequence.
123
00:06:16,320 --> 00:06:20,220
So wherever the sequence is
increment with the nextval
124
00:06:20,220 --> 00:06:24,130
modifier and go ahead
and give us that value.
125
00:06:24,130 --> 00:06:28,770
Notice that we started
with 6 and used nextval,
126
00:06:28,770 --> 00:06:31,170
and yet that still
gave us the value of 6.
127
00:06:31,170 --> 00:06:33,420
Because the first time
you use a sequence,
128
00:06:33,420 --> 00:06:36,460
it has to be initialized
with that value.
129
00:06:36,460 --> 00:06:39,140
So if we try a
similar statement.
130
00:06:39,140 --> 00:06:45,500
Copy, and then we'll make this.
131
00:06:48,990 --> 00:06:52,300
So again, just
referencing the sequence.
132
00:06:52,300 --> 00:06:54,410
Row is inserted.
133
00:06:54,410 --> 00:06:58,150
And now it's incremented to 7.
134
00:06:58,150 --> 00:07:01,180
We can also use the
CURRVAL modifier
135
00:07:01,180 --> 00:07:05,050
to see what the current
value of a sequence is.
136
00:07:05,050 --> 00:07:07,240
So to do that, I
use the dual table.
137
00:07:07,240 --> 00:07:14,100
And I can do select
salgrade_seq currval--
138
00:07:14,100 --> 00:07:18,530
C-U-R-R-V-A-L-- from dual.
139
00:07:18,530 --> 00:07:22,080
It show us that the
current value is 7.
140
00:07:22,080 --> 00:07:27,840
In a similar way, I can use
NEXTVAL to actually advance
141
00:07:27,840 --> 00:07:29,880
the sequence.
142
00:07:29,880 --> 00:07:37,070
So select nextval for dual,
now it's 8, 9, and 10.
143
00:07:37,070 --> 00:07:40,170
And if we look at
the CURRVAL, it's 10.
144
00:07:40,170 --> 00:07:44,700
So if we were to go
one more, the sequence
145
00:07:44,700 --> 00:07:46,860
has already been advanced.
146
00:07:46,860 --> 00:07:52,510
Now we insert, and select, and
notice that the value for grade
147
00:07:52,510 --> 00:07:55,300
was advanced as we
used NEXTVAL to advance
148
00:07:55,300 --> 00:07:57,400
the sequence a couple
of times, and then
149
00:07:57,400 --> 00:08:01,660
we used NEXTVAL to reference
it, and so it advanced it again.
150
00:08:01,660 --> 00:08:03,610
But again, the most
important thing
151
00:08:03,610 --> 00:08:05,830
here generally in
these columns is
152
00:08:05,830 --> 00:08:07,540
that there are no duplicates.
153
00:08:07,540 --> 00:08:11,200
So if you have a standard in an
organization, where the coding
154
00:08:11,200 --> 00:08:13,080
standards use sequences--
155
00:08:13,080 --> 00:08:15,460
every one, every
user, every developer
156
00:08:15,460 --> 00:08:17,830
can access those
sequences-- and there
157
00:08:17,830 --> 00:08:22,030
may be gaps, which is usually
less problematic than having
158
00:08:22,030 --> 00:08:23,290
duplicate values.
159
00:08:23,290 --> 00:08:25,960
But you can ensure
that primary key,
160
00:08:25,960 --> 00:08:30,730
that synthetic primary key, is
consistent across every table.
12861
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.