You saw earlier that keys are just fields. In the previous examples, all the keys were unsigned integers. Well, keys can also be varchar or char fields.

In this next example, we'll use varchar fields and we'll use the lastName as the primary key, instead of nameId. In this example, we can't have more than one of the same lastName in the names table. For example, if we have two different "Smith's," we won't be able to add the second. This is because keys need to be unique.

Let's create a new database:

create database employees2;

use employees2;

We'll create two tables:

create table names (
	lastName varchar(252) not null,
	firstName varchar(252) not null
) engine=innodb default charset=latin1;

alter table names add primary key (lastName);

create table addresses (
	lastName varchar(252) not null,
	street varchar(252) null,
	city varchar(64) null,
	state varchar(2) null,
	zip varchar(12) null
) engine=innodb default charset=latin1;

Now, let's add some data:

insert into names set firstName='Jim', lastName='Smith';
insert into names set firstName='Bob', lastName='Rocker';
insert into addresses set lastName='Smith', street="1234 Main St", city="Los Angeles", state="CA", zip="90000";
insert into addresses set lastName='Smith', street="1234 Main St", city="Los Angeles", state="CA", zip="90001";
insert into addresses set lastName='Rocker', street="5678 Crazy Town St", city="Los Angeles", state="CA", zip="90010";

Now that we have all that in there, let's select some data from the database, but instead of using id=nameId, we'll use the lastName as our key:

mysql> select names.lastName, addresses.zip from names, addresses where names.lastName = addresses.lastName;
+----------+-------+
| lastName | zip   |
+----------+-------+
| Smith    | 90000 |
| Smith    | 90001 |
| Rocker   | 90010 |
+----------+-------+
3 rows in set (0.00 sec)

As you can see, we used text as our Primary Key and everything just worked. A lot of database systems are designed this way and there isn't necessarily anything wrong with doing it this way, however there are a couple of downsides.

Let's think about data types for a minute. From C++, you know that an unsigned integer is 32 bits. 32 bits gives you over 4 billion different numbers.

If we use unsigned integers as our Primary Key, we get over 4 billion different records that can be stored uniquely. And we get it all in four bytes of space per row.

Now, think about using text. In our example, lastName used "Smith" which is five bytes of data. Already, we've used more space to do the same thing. Most, but not all last names are longer than four bytes or four characters.

Now, think about what happens underneath the hood in MySQL when it's searching for a Key. It basically just executes a loop over every single record and tries to match what you asked it to match. Iterating over an unsigned integer is going to be much much faster than trying to do the same thing over text.

You'll get significant speed gains by using unsigned integers, compared to other data types.

This lesson was more to show you that it can be done and in certain cases doing it this way might be advantageous. When designing your database system, it's smart to deeply consider what type of Primary Key system you might want to use before you create your project.

There are also a lot of existing systems out there that use varchar primary keys.

---

The other side of the coin is that speed isn't necessarily everything. Storage space isn't either. If you only had 1,000 employee records, then a modern server will be able to search through those records in microseconds. So, in this light, this might be a better system. In fact, I wouldn't even bother with indexes for this size table.

This particular style of database keys also make things more simple and maintainable. It's quite obvious what the join keys are and writing maintainable software would be quite a bit easier.

As always, it just depends on your project, the project requirements, and the situation at hand.