Original subtitles

In this lesson, we'll take a look

at sequences in a database.

So to begin our discussion of sequences,

let's talk about a couple of terms here,

natural keys versus synthetic keys.

So a natural key is a value in a database or column

value that actually uses the true data that's

being stored in the database.

So if we have an employee table, and we

have a column for social security number,

we could use that as a primary key.

Because we make the assumption that value for social security

number would conform to the rules of a primary key,

mainly, that there are no duplicates allowed.

So there's no duplicate social security numbers.

A synthetic key is a key value that we add, say,

for a primary key that is completely generated.

So it's synthetic.

It has no relationship to the data itself in any real way,

except to act as a primary key value.

So there's pros and cons for both ways when you do this.

But it's really important, in my opinion, to choose a method

and stick with it.

So natural keys have the benefit of using less space,

and they don't require an additional column.

It's only the space that would be used in a synthetic key, so

that key value, or the key values

throughout all the tables in the database.

But there is some savings there, and also you

don't have to have that extra column.

Synthetic keys, they're going to actually abstract the values

from the actual data.

So to give you an example of a coding standard I've

seen in the past is you have a data model with many tables,

and every table has to have a primary key.

In many cases, those primary keys

can't be established from the data that's

in those tables themselves.

There may not be any column value

that can be used to actually make a primary key value

or to make a value that allows that value

to distinctly identify that row from all of the other rows.

And so, since the rules were that a table must

have our primary key, every one of the tables

had a synthetic key for the primary key.

So a sequential number, 1, 2, 3, on and on,

was generated for that table.

And then that table had the primary key values

that were actually separate from the data.

They didn't have any relationship.

But they could function in the way

that a relational table needed to.

In my opinion, synthetic keys are beneficial,

because there are so many problems in data models

trying to find a column or columns that can uniquely

identify every row in a table.

So if we choose to use synthetic keys,

the sequence can be very important.

So a sequence is a database object that

generates sequential numbers.

And that's really all it does.

Now, of course, if we decided to use synthetic keys,

we could have our code constructed

to go out and search for a value, the greatest

value in the table, increment by one, and then insert that row.

But a sequence keeps us from having

to do that, because we can just reference the sequence itself,

and say, give me the next available number

in the sequence.

And so we use a couple of modifiers, NEXTVAL and CURRVAL

in order to advance our sequences.

So this is much easier to show than it is to explain.

So let's take a look at what we have here.

Let's connect to our Scott user, and we're

going to do select star from salgrade order by grade.

So we have this grade column, which is incrementing numbers.

So these are salary grades.

So grade 1 has a low sal of 700 and a high sal of 1,200,

and so on, and so forth.

So if we wanted to add more salary grades,

we need to make sure that grade--

if we're going to use grade is the primary key--

is never a duplicate.

So we can't have another grade 5 with different high sal

and low sal.

Now again, we could go out in our code,

and we could select the max value for grade, increment

by one, and then do the insert.

Or we could use a sequence.

So let's say, select max(grade) from salgrade.

Now we can see it.

But just to establish this, let's

say, OK, that's the maximum value for the grade

column in salgrade.

Now I'm going to attempt to create a sequence.

Salgrade_seq, and we put INCREMENT by 1,

and start with 6.

Why do we start with 6?

Well, because our max value right now is 5.

So we're going to use a sequence to insert data into salgrade

from now on.

And so, that's what we want to do.

We have the sequence name.

Increment by whatever value we want.

In this case, we'll increment by 1.

You can increment by 2, or 5, or 10, or whatever.

And then we have the starting value.

So let's attempt to create this, and the sequence is created.

So how do we use a sequence?

How do we reference it?

Well, let's say we have a new salary grade to put

into the salgrade table.

So we could say, insert into_salgrade values.

And instead of putting a literal value here,

we're going to use the sequencer name, salgrade_seq,

and the modifier nextval.

And then we'll put our high sal and low sal to be consistent.

The row's inserted.

Now we select, and we see that we have now grade 6

with the low sal and the high sal that we requested.

So we didn't put a literal 6 into the insert statement.

We just referenced the sequence.

So wherever the sequence is increment with the nextval

modifier and go ahead and give us that value.

Notice that we started with 6 and used nextval,

and yet that still gave us the value of 6.

Because the first time you use a sequence,

it has to be initialized with that value.

So if we try a similar statement.

Copy, and then we'll make this.

So again, just referencing the sequence.

Row is inserted.

And now it's incremented to 7.

We can also use the CURRVAL modifier

to see what the current value of a sequence is.

So to do that, I use the dual table.

And I can do select salgrade_seq currval--

C-U-R-R-V-A-L-- from dual.

It show us that the current value is 7.

In a similar way, I can use NEXTVAL to actually advance

the sequence.

So select nextval for dual, now it's 8, 9, and 10.

And if we look at the CURRVAL, it's 10.

So if we were to go one more, the sequence

has already been advanced.

Now we insert, and select, and notice that the value for grade

was advanced as we used NEXTVAL to advance

the sequence a couple of times, and then

we used NEXTVAL to reference it, and so it advanced it again.

But again, the most important thing

here generally in these columns is

that there are no duplicates.

So if you have a standard in an organization, where the coding

standards use sequences--

every one, every user, every developer

can access those sequences-- and there

may be gaps, which is usually less problematic than having

duplicate values.

But you can ensure that primary key,

that synthetic primary key, is consistent across every table.