Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
I welcome back to module to this is less than 2.6 key types, and this less than we're going to review the common key types that you run across in databases. So let's go ahead and take a look at these. The 1st 1 is the natural key. This is the key that front end users are typically familiar with.
00:18
For example, if you are American, you have a Social Security number. This number represents you, and this would be an example of a natural key. It's the key that we are all familiar with
00:30
now in other database applications. This is typically a var char field with a clear pattern
00:37
such as E P 00001
00:42
The unique constraint is commonly applied to this key when it is in the data base so that it can't be duplicated.
00:51
The surrogate key
00:53
is a key that is automatically created by the database. For example, it's the ever common auto in committing I D Field that you commonly come across in databases
01:03
and the next bullet just reaffirms. What I just said is that it's typically an auto incriminating numeric field,
01:11
the primary key, while either the surrogate key or the natural key could be used as the primary key. It's up to the database designer to decide which one is appropriate in a given situation.
01:25
Okay, so let's take a quick look at Thesis Armory, where we have some more information on key types.
01:33
So when in doubt, always use a surrogate key. If you're unsure if you need a surrogate key, just go ahead and use one.
01:41
The problem with natural keys is that front end users commonly want to change their form to fit some look,
01:49
for example,
01:49
a manager might state that they want employees to start with a nine instead of a one.
01:56
And the reason you want to avoid using a natural key is because you don't want to have to deal with changing the key, the primary key in the database in the wrong database, making it happen to make a change to a bunch of primary keys. My intel changing the references across hundreds of pivot and transactional tables.
02:15
And that is a risky maneuver to take.
02:19
And the reason it's risky is because if something goes wrong, when you're changing those primary keys over
02:25
you, you risk scrambling into data in an unpredictable way.
02:30
So if you set up your database with a natural key
02:34
and a surrogate key,
02:36
and they want to make a change to the natural key to fit some look, all you have to do is change that one natural key field on that table. And because the surrogate key is left alone and the surrogate key is used across all those transaction tables, you don't have to worry about changing any of those tables data to
02:55
facilitate that new look that that manager might want.
02:59
So
03:00
in summary,
03:01
when you're looking at using a natural keys or a surrogate key and you're not sure which one to use, always generate a surrogate key to go along with that natural key.
03:12
And that completes this lesson, and I hope to see you in the next thank you

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor