Key Types

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.