Not all data is equal, and keys are the rulers of your database tables. Keys bring order to tables and speed up searches.
If you think of tables as chapters in a book, then keys are like line-numbers. Keys tell you how to get to the place where the data you need is stored. The most important key is the primary key.
Primary Key (PK)
This is a unique identifier for every row of data in your table. A primary key cannot be null and it is best to have one column, though it is possible to have more.
In some tables a key maybe an automatically generated number by a built in function. The field will incremented by one each time a new record is added. This is known as a surrogate key, where a field with no business value is created for the sole purpose of being the primary key.
If possible, it’s good to have a more significant primary key. For example, in a table containing information about your equipment, you could use the serial number of a device. You know the serial number will always be unique and, as you may look up a serial number, having it as a primary will speed up your search. This is known as a natural key.
Foreign Key (FK)
As you know, a Relational Database (RDBMS) is a store of data in linked tables. Keys provide the linking mechanism. By including another table’s Primary Key as a column you make it possible to lookup data from the other table, this is a foreign key. So, if you have Orders table and you include the primary key from your Customers table you have created a link to lookup the customer’s information.
An understanding of these two types of keys is important for understanding databases. These are the most common keys types used, but there are other types you may hear mentioned:
Composite Key Where two, or more, columns make the primary key. For example, house number and postcode.
Candidate Key Any column that could be used to uniquely identify records. Your primary key will come from one of these columns.
Alternate Key This is any column which is a Candidate Key, but has not been made the Primary Key.
Unique Key A column that contain unique data but can contain Null and therefore cannot be the Primary Key.
Keys are vital because they allow us to:
identify any row of data
Establish a relationship between tables
enforce identity and integrity
At DB Backbone we want to help you get an understanding of the important role databases play in our lives. If you have any you want us to look at, please add a comment below.
Related Posts
