3.16 Primary Keys

00:00

I welcome back to Model Three sequel programming were in less than five. Tables were moving into Sub Lesson 5.3 primary keys

00:09

and the previous lesson. We created a simple small table within the D Beaver environment, but we left out an important feature. In fact, this is a future that every table within a database should have, and that is the primary key.

00:23

The primary key has a very special purpose, and that purpose is to provide a unique value for every record in the table, so that when programmers work with those database tables, they can use primary keys and foreign keys to know what data belongs to, what data from one table to the next table.

00:43

So their purpose is really for communication between tables. Now, for example, let's say that Social Security number was a primary key.

00:53

Everyone in the United States is supposed to have a unique Social Security number. We didn't allow it to be no, so it's not allowed to be knows. And that's another important feature of the primary key is the camp. You know,

01:03

I need to be unique,

01:07

so if we then had an address table

01:08

and you saw your Social Security number in that address table. Next to address fields such as, you know, ST ST ST City, you would know that address belongs to you. And what we've done then is we've created a address model and a person model and those air tables, and we connect them by this Social Security number.

01:29

Now, this example, we're not going to use this. Oh so Social Security number. We're going to use what you more commonly see, which is a special I. D field that just gets a unique value for every record that you add.

01:42

So let's go ahead and start doing that.

01:45

So we're going to add a new column.

01:49

We're going to call it I D.

01:52

Now. We don't want it to be native, and we'll go ahead and give us some more memory. So we'll say you can have more memory by saying bigots

02:00

Now The size of that imager varies can vary by system and database. We just know that it's going to be larger than a regular indenture. Never say. Do not worry about negative numbers. Use all available memory to reach the highest positive number that you could possibly reach if the database table grows at large

02:19

and in our example, it won't.

02:21

And we're gonna say it's not no

02:23

has to be filled in an auto increments. We don't have to worry about this column. It ah, increments by itself.

02:30

I'm gonna go ahead and click, Okay,

02:30

Now, if you try to click save now, we will get in there.

02:37

So persist.

02:38

And that's because there's a rule that says the auto column and must be defined as a key, which is what we need to do. And to do that in d B, we're gonna go into the constraints area by clicking on constraints. We're gonna right click and we're gonna say, Create new constraint

02:55

And it's already got

02:58

some of the options selected that we need. There's gonna be a primary key

03:00

personnel to score Piquet for Primary Key

03:04

and I'm gonna select I d

03:07

and Click. OK,

03:08

so what we've done if he created a primary key by creating a new column called I D

03:15

give it a data type of big ant, unsigned

03:17

said, You can't be no and you auto increment.

03:21

We then went into constraints area

03:23

and we defined our constraint by saying this column

03:29

I D is the primary key. Now let's go ahead and click save, and this time it will work

03:36

persists.

03:40

Okay, it looks like we hit a little bit of a snag. Let's go ahead and get this fixed. So what it's happening is De Beaver is trying Thio

03:50

create the auto increment column,

03:53

and it's failing on this part right here. It never sees this part.

03:58

So let's go in and fix is on the way we're going to do that

04:00

is we're going to you

04:05

get rid of the auto increments Check box,

04:10

click save.

04:11

Persist

04:12

now it saved it.

04:14

It is a primary key, and now we're going to turn on the auto increments and click, save

04:20

and click persists.

04:24

And there we go. So we had to break it apart a little bit, because when we tried to do it all at once, the beaver did not automatically arrange it in the right order that my sequel would understand.

04:34

And that happens from time to time in various environments. And it's why it's a good reason Thio get used to looking at the code and understanding what might be going wrong,

04:46

because sometimes in these environments, they do it. They they offer a lot of help.

04:50

But sometimes things don't get arranged quite right. And you have to break it apart.

04:56

And, ah, adjusted a little bit to make it work. And usually, if you're in an environment like that, you can just start to take it one step at a time, which is, you know, one step, save one step save and get to your destination.

05:08

Now, to recap on what we covered in this lesson, as we briefly discussed primary keys in their purpose, Primary key every database tables should have won. Its primary purpose is to designate

05:19

a unique value for every record they don't repeat.

05:25

And they can't be empty.

05:27

And they're very important.

05:29

And their importance is for communication.

05:31

Across tables by a primary keys and foreign keys. We will discuss foreign Keyes and the next lesson.