Microsoft Access 2013 Tutorials
Database 101 / Primary Key Basics
Subtitles of the Movie (Microsoft Access 2013 / Database 101 / Primary Key Basics)
Now you won't work with databases very long before you're going to hear the term primary key. And this is a fundamental piece of database design and so in this video I want to give you a real simple overview of a primary key. All databases share one common characteristic, they grow. And once they start growing they tend to like gain momentum somehow and they grow faster and faster and faster. Now the more data they contain the longer it takes to work with that data and that just makes sense. If I'm indexing it takes longer to index more data. If I'm searching for something it takes longer to search through more data. And so what I want to make sure is that as the data builds up and I have to work with that data that I've only got necessary data in there. Because if I have my data stored in a certain order and I have to add new data I have to add the data in the right location. Or I have to find the data when I've got an order or a query that wants to modify data. Okay. So as I get more data it becomes more important that I have necessary data in there the right way. And here's the main problem storing duplicate data is a bad idea. It's one thing to keep a copy of the newspaper. Alright. It's another thing to keep 50 copies every week of the same newspaper. Right. You're really starting to inefficiently use your storage space. Now duplicate data is real easy to spot when you have five rows right? So if I give you names, five names and you write them down on a piece of paper, you can glance at that and say, oh wait a minute Mark you got, you got two Bob Smith's there. Right. Well what happens when I have 5,000 names, how do I know that I'm not duplicating my data and causing a problem? Well the primary key comes to save the day here, it solves the problem. And let's look at a little example here. I have that same Employees table and you'll notice I've got Jack Jones, Ashley Harris. Right. Christian Smith and Bobby James in here and I've got some other data here on them as well. Well I want to add another Jack Jones in here and notice that somehow I've failed to realize that I already had Jack Jones, pay grade 7, Social Security Number 416873643 already in the database. Well if I add him again and let's say there's a bunch of rows between this, maybe there's, there's a break here, maybe 2,000 rows between Bobby James and Jack Jones. But I can't tell by looking at this if I have enough rows that it's a duplicate. But I've got duplicates and then later one day I might change you know Jack to John and there's Jack in one place and John in the other and it becomes a problem and just all kind of issues. And so we're going to now add a primary key to this table and one or more columns can make up the primary key. Alright. In our example here we're going to make first and last name columns the primary key. Now what this means is anytime a row gets added to this table the first and last name together must be different from any other row in this table based on those two columns. What that means is when I put Jack Jones in here that violates the primary key and it says oh wait a minute, wait a minute dude you can't put Jack Jones in here. Right. Because you've already got a Jack Jones up here. What would happen if you tried to insert this row into this table with a primary key and this would be called a composite primary key because it's on more than one column. This primary key says that, hey based on first and last name you can't have two Jack Jones. Now is this a problem? Could be. We might want to take this a little farther and say include pay grade there. So I can have Jack Jones at 7 and a Jack Jones at 6. We could have two Jack Jones but again we're kind of back to design issues on our tables, you see that? But anyway that's what a primary key does. So the primary key says no you can't put two Jack Jones in here so we're going to take that one back out. And so a primary key ensures that I don't have duplicate rows in my tables.
Tutorial Information
| Course: | Microsoft Access 2013 |
| Author: | Mark Long |
| SKU: | 34405 |
| ISBN: | 978-1-61866-090-9 |
| Release Date: | 2013-02-01 |
| Duration: | 7 hrs / 89 lessons |
| Work Files: |
Yes |
| Captions: | No |
| Compatibility: |
Vista/XP/2000, OS X, Linux QuickTime 7, Flash 8 |
VTC Sign up & Benefits
- Unlimited Access
- 81,350 Video Tutorials (14,200 free)
- Video Available as Flash or QuickTime
- Over 715 Courses
- $30 for One Month Access
- Multi-User Discounts Available
