Learn SQL Database Programming

Natural and surrogate primary keys

Now let's talk a little bit more about choosing a primary key. There are two types of primary keys: natural and surrogate. With a natural key, you are using unique columns, and the data in those columns exists outside the database (that is, in the business world). With a surrogate key, you are creating a column to hold a unique value for each row, and that value isn't used anywhere outside the database.

If there was an obvious choice for a primary key (remembering that this is unique and not null), we could use that column. For instance, we could use an obvious primary key if one of these tables contained information about books. Each book has a unique, not null ISBN. This natural key would be an excellent ...