From the course: Database Foundations: Data Structures

Natural keys

From the course: Database Foundations: Data Structures

Start my 1-month free trial

Natural keys

“

- [Instructor] Now that we know how to create a primary key column, it's worth spending a few minutes to discuss the different kinds of data that work well for providing the tables key values. The first kind of information that you should consider is called a natural primary key. The only requirement for a primary key is that each row will have a unique value in that column. Sometimes you'll find that you're already storing a column in your dataset that's guaranteed to be unique for every record. In these situations, there's no reason to reinvent the wheel, you can simply designate that column as the table's primary key. Here's an example. When you sign up for an account with an online store, you're often asked to give your email address and create a password. Email addresses are often treated as unique identifiers in a customer database, since two people can never have the same email address. In this case, creating a unique serial number for the primary key is unnecessary. Instead, just use the unique email address that you're already going to be storing. When determining which column to use for your primary key, you need to look beyond the data that you already have and project your needs far into the future. Ask yourself if this table is storing millions of rows, is there even the slightest possibility that a value might be repeated in this column? If not, then it's a good candidate for serving as the table's natural primary key. You have to be careful with this question though, in a small office, you might think that you can get away with having employee names be the unique identifier, but it's not uncommon for two people to share the same name and you never know who's going to be hired next week. It's also not unheard of for two employees to have the same birthday. In fact, it's not impossible for two employees to have the same name and the same birthday, given an infinitely large population. So those kinds of values are not good candidates for a table's primary key, but social security numbers are unique, driver's license numbers are unique, either of those would work well if you're already storing them in your database. When identifying whether or not you can use a column in your table as a natural primary key, your goal is to find data that's impossible to duplicate, not just unlikely or improbable. So the first type of data that you should consider for your primary keys is something that you're going to be storing anyway. Look for unique values that are naturally occurring in your data already to identify each row and create the connective tissue that binds your data tables together.

Contents