MySQL Primary Keys
Explains what a primary key is, and why it's an integral part of relational databases.
When we created our two tables, we also created a primary key for each table.
When you expand the nodes in the left SCHEMAS tab, you can see the primary keys (and foreign keys—we'll get to those next) listed under each table:
As the accompanying screenshot shows, the Indexes nodes contain any indexes (in our case foreign keys and/or primary keys). These primary keys and foreign keys are only there because we specified them in our code when we created the tables.
Specifically, we used PRIMARY KEY (FruitId) to make the FruitId column the primary key for the Fruit table, and we used PRIMARY KEY (UnitId) to make the UnitId column the primary key for the Units table.
What is a Primary Key?
A primary key (also referred to as a unique key) is a column that has been allocated as the unique identifier field. The value in a primary key column is unique to each record. In other words, no two records can share the same value on that column.
A classic example of a primary key field is an "ID" field. Most tables will have an ID field that provides a unique identifier for each record. Examples could include "CustomerId", "ProductId", "FruitId", etc. Without ID fields like these your database's function would be severely hampered. If you had two or more customers with the same name, how would you be able to find their record? While it's true that you could possibly find something unique about every record, it's a lot cleaner and easier to have a column whose primary purpose is to provide unique identification for each record.
A primary can can either be a normal value that is guaranteed to be unique (an ISBN number for books, product code, etc), or it can be a value generated by the application or DBMS specifically to be unique (such as a globally unique identifier, or auto incrementing integer).
Choosing a Primary Key
Be careful when choosing a column for your primary key. You need to make sure every record will have one, and that there's no possibility that two records will share the same value, or that one record will have more than one value.
For example, using the user's email address might work in some cases, but it's not a very solid basis for a unique identifier. Users can change their email address. Users can share email addresses. Some users might not have an email address. Of course, you could write your system to prevent users from changing their email address or sharing it, but your system wouldn't be very flexible or user friendly.
You could require all users to have a unique username. That could work. However, you need to think carefully about all possibilities. If there's any chance users may change it or share it (past, present, or future) don't use it as a primary key. What if "TechGuy12" de-activates his account. Does that mean another user can now use "TechGuy12"? Will that be a problem for your application or any reports you need to generate?
If you're in any doubt about the "uniqueness" of your primary key field, use an auto generated number that increments with each record created.