From the course: Office 365: Access Essential Training

Understanding primary and foreign keys - Microsoft Access Tutorial

From the course: Office 365: Access Essential Training

Start my 1-month free trial

Understanding primary and foreign keys

“

- In a well designed database, you'll create many tables that relate back to records in other tables. If you have data that isn't specifically about the primary theme of the table, then it's probably the case that it should go into another table. For instance, let's supposed we want to sort credit card information in our database. Here's a couple of questions that you might ask when determining whether or not to store the credit cards with the guest ID's. Does a credit card number describe our guest? Well not really. Would every guest want us to remember their credit card number? And probably not. Will some guests have multiple credit cards on file with us? And I would say yes, that's a possibility. So credit card numbers are not specifically about the guest. Some guests wont have any credit cards and some guests will have many credit cards. All of these answers lead me to conclude that credit card information should be stored in a different table from the guest information. So let's go ahead and create a table to store the credit card info. I'm going to go up to the Create tab here, and I'll press the table icon here. Access starts me off with a brand new table and it automatically created an ID field for this and set it to AutoNumber. If i click on it here, you'll see the the data type is listed as AutoNumber. I want to change the name here to credit card ID instead of just regular ID. That'll be a little bit more specific about what this table is storing. The next field over here, is going to be the credit card name. I'll go ahead and type it as a Short Text field here. And then type in CardName. I'll press Tab to go to the next field, and here we're going to type in the card type. And again, this will be a text field. Press Tab again and the next column is going to be the card number. But instead of storing it as a number, I'm going to store it as text. We obviously wouldn't want to start adding credit card numbers together, so it's more likely it's going to be stored as a text field. So I'll go ahead and select Text, and type in the field name of CardNumber. One more time I'll press the Tab key, and the last field is going to be the expiration date, so this will be a date and time data type here. So I'll select that from the list, and we'll type in ExpirationDate. I'll press enter here to go into the last field and we need to make sure that we create a way to tell us what customer this card belongs to. We could put in a name field but what would happen if we had two guests with the same name? How would we know which of them this credit card belongs to? This is why we have a unique primary key in each table. We can make use of them in other tables so that we can know exactly which guest this card belongs to. In this case, this is called a foreign key in the credit card table, since it tells us who each credit card is connected with. Let's go ahead and create a foreign key here called Guest ID. And since we're connecting it to the auto number field in the guest table, we're going to select a data type of Number here. So the data matches up. So I'll go ahead and type in GuestID. I'll go ahead and press enter. And that's the last field that I want to create so I'm going to go back and just click on this GuestID column here to get rid of that menu that's popped up. If I want to rearrange my columns all I need to do is click and drag, so I've clicked on it and then I'll click again and drag over. So I'll take the GuestID and I will put it right after the CreditCardID. Typically when you are creating your tables, you'll want the first column to be the primary key for that table. And usually the second column or the second and third columns will be the foreign keys that'll link to other tables. It's not required to do it that way, but that's typically the way that it's done. So at this point, I'm going to go ahead and save this table here. So I'll just press the save icon here on the quick access toolbar. And I'm going to call it GuestCreditCards. That adds it over here into my navigation pane. And now I want to start entering in some information. Before I do that, let's take a look at our Guests information, so I'll double click on the Guests table to see that we have three guests currently in this table. Katherine is number 1, Donald is GuestID number 2, and Sarah is GuestID number 3. Let's go ahead and add in a couple of credit cards for Sarah who is GuestID 3. I'll switch back over here to the GuestCreditCards. And so for the GuestID I'll go ahead and type in the number 3. CardName is going to be a business card. It's a Visa. And I'll just type in a bunch of zeros here for the CardNumber. And the expiration date is 10/31/18. I'll press enter to go down into the next line and then I'll press Tab to get off of the ID field which I can't type in anyway. We're going to enter in a second credit card. So again, this is going to be GuestID number 3 because it connects to the same person. Press Tab here. This is a personal debit card. It's also a Visa. And again I'll just type in a bunch of zeros for the number, and the expiration date is 3/31/19. So notice that this structure is very flexible and efficient in allowing us to store one, many, or no records that are related to each of our customers. And the order that the cards are entered doesn't matter at all. If Katherine, our guest with the ID number of 1, decides later to put in a card, that's completely fine. It can go anywhere in this table as long as it's tied to her GuestID. So if we wanted to add in a new card for Katherine, we'll just type in GuestID number 1. So adding foreign keys to your table is just like adding any other field. The important point to keep in mind is the that data types must match. This means that if you're using an AutoNumber field as the primary key in one table, then the foreign key in the related tables must be in number field. As long as the data is the same, you can use one as the reference to look up information in the other.

Contents