We’ve discussed primary keys numerous times throughout this series, but this video is going to be devoted to going over primary keys in more detail and cover all parts of them. Rather than a piece here and there, you can eat the whole pizza!
A primary key is a column that every table in your database is going to need. The goal of a primary key is to force every row to be uniquely identifiable. It is a way to identify each row. In order for this to work though, there are a few qualifications that are needed for a column to be a primary key.
First, every row has to have a primary key. That means that the PK column cannot be null. The second is that every row has to have a unique primary key. That means that the primary key column has to be labeled UNIQUE.
You can essentially think of a primary key as the combination of the NOT NULL constraint and the UNIQUE constraint and you can only have one per table.
Lastly, the primary key should never change. There is a way to force this in our database that we will talk about shortly. But think about the reasoning. If a primary key changes, it is hard to tell whether it is the same entity with a new key, or a new entity. You might think, well, obviously it is the same entity with a new key because the data for the entity is exactly the same, but if we are following the conventions on primary keys, we know they shouldn’t change. If we know that a key shouldn’t change, and it does, our thoughts are likely that there is a new entity.
Now, there are two types of primary keys. Surrogate keys and natural keys. Surrogate keys are computer generated and mean nothing outside of the database. Natural keys are when we use data that is real to uniquely define something. Each have their benefits. The benefit of surrogate keys is that they easily meet all qualifications for a primary key. The downside to surrogate keys is that it makes reading data a lot harder. The benefit of natural keys is that it makes data easier to read, but it can be a challenge to find data that is going to be UNIQUE, NOT NULL, and never change.
Now how do you tell MySQL that you want to create a primary key? You have to use the PRIMARY KEY keywords for this column when you are creating the table. We’ll be making primary keys when we get to the CREATE TABLE statement, but for now understand that you have to explicitly tell MySQL that a column is a primary key.
Subscribe to my newsletter: