Database Table Keys
Database Essentials - Part 2
Division 1
Forward: In this part of the series we look at what is known as a key.
By: Chrysanthus Date Published: 4 Aug 2012
Introduction
A Key
A key is one column or a set of columns in a table that identifies the rows of a database. In the previous tutorial the EmployeeID column of the employee table is a key. Each ID in the column identifies a row. The key of a table is normally at the left of the table.
The Primary Key
A Primary Key is one column or a set of columns that uniquely identify rows of a table. An ID column is not a primary key if all the IDs (numbers or code texts) are not unique. If a primary key is made up of one column, then each value in the column must be unique. In that way, each value uniquely identifies a row. If an ID column such as the EmployeeID column mentioned above has unique IDs (values) then the ID column is a primary key.
The reason why we need a primary key is to be able to identify rows uniquely. An ID column will identify a row but the IDs are not necessary unique. In many tables you have the same value in different table cells. Imagine that you have a table, which gives information about students in a class. You might have a column in the table that has the age of students in a class. In that same column there will definitely be cells with the same age (value). So there is the need to have a primary key in a database table the uniquely identifies a row. Any database table must have a primary key, which may be one or a set of columns.
As mentioned above, a primary key can be made up of one column or a set of columns. When a primary key is made up of a set of columns, it is called a Composite Key. This composite key is usually at the left of the table. You will have to open the following link in a new browser window tab to see the tables and diagrams for this tutorial. As you read this tutorial, you will have to be referring (clicking) the window tab (page) to see the illustrations (link below).
In table 2.1 of the window tab page, there is no ID column. This is the first Employee table we saw in the first part of the series. Look at the Last Name and First Name columns. Normally, the list of employees can be long. The above list is short. It is possible that more than one employee can have the same last name. So you cannot use the LastName column as a primary key. It is possible that more than one employee can have the same first name. So the FirstName column cannot be used as a primary key.
If you know the number of employees in your organization, then the list will not be long, and so it is unlikely that any two combination of last name and first name will be the same. In this case you can use the combination of LastName and FirstName column as your primary key. Here the pair of cell values in the two columns uniquely identifies a row. When a primary key is made up of more than one column, it is called a composite key. In this case you composite key consists of the LastName and FirstName columns.
Well a database table is design to last forever (in theory). So as you replace workers or as your company grows and you add new workers, it is possible that someday two or more employees will have the same last name and the same first name. So using the last name and first name as a table primary key, is not a good idea. And so, do not use last name and first name as composite keys. I have done so just for the purpose of illustration. As we go along in the course, you will see practical composite keys.
With some tables you can find a column that has unique values and you can use that column as the primary key. With other tables you cannot find a column with unique values. In that case you need to create a new column and give it unique values; then use the created column as the primary key. It is possible for the Database Management System (DBMS) in the computer to do that for you. When it does, we say the primary key is a Surrogate Key. Surrogate keys are usually numbers. In many cases they are just simple counting numbers, i.e. 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, etc. The DBMS may call this auto-numbers.
Foreign Keys
A Foreign Key is a column in one table, which is a primary key in another table. The foreign key can be used to link the two tables together.
Table 2.2 shows a transaction table for a store that rents video CDs. A Customer rents a video CD, watches it and then brings it back to the store. For simplicity, assume that one video has a whole film. When a customer rents a CD, information is typed into the transaction table from a worker at the store. There are three columns in the table. You have the transaction ID column (TransID), the column for the date, the CD was rented (RentDate), and a Customer ID (CustID) column. The TransID column is the primary key for the transaction table. It can be a Surrogate Key. Look at this table very well. The CustID column is not the primary key for the Transaction table. It has numbers, which are values of the primary key of the Customer table of Table 2.3.
The Customer Table and the Transaction Table are two tables of the same database for the store. These two tables have nothing to do with the first table of Table 1.1. The Customer table has the addresses (and phone numbers) of the customers to the video CD store.
The customer table has a customer ID (CustID) column. This is the primary key for the customer table. This primary key can also be a Surrogate Key. The rest of the columns for the customer table are for the address values. In the transaction table, the CustID is a foreign key. In the customer table, CustID is the primary key. In the Transaction table, the CustID column does not have to have unique values; it is a foreign key and not a primary key.
If the database user (store owner) wants to know the full address of the customer who made the second transaction, the DBMS will start at the TransID number, 2, and then move along the row to the CustID column. At the CustID column it would see the CustID value, 7. The DBMS will now go to the customer table at the CustID primary key column, and identify the CustID, 7. It will then read the contents of the cells of the rest of the row belonging to CustID, 7 as the full address of the customer.
A database designer first designs a table on paper before it is implemented as code in the computer. When you do the design on paper, in order to differentiate the primary key name from the names of the other columns of the table, you have to underline the name of the primary key. In Tables 2.1, 2.2 and 2.3, the primary keys are underlined.
Conclusion
The ID is just a convenient way to identify rows of a table. The IDs of the ID column do not have to be unique, even though most IDs are meant to be unique. A key is like an ID but it does not have to be a number or code (e.g. MSE5). A key column can be made up of IDs or other values (text). The word, “key” in database is not crucial. What is crucial is “primary key” and “foreign key”. There are three types of primary keys: the ordinary primary key, which is one column, the composite key, made up of more than one column and the Surrogate Key, which is also one column. A Foreign Key is a column in one table, which is a primary key in another table. The foreign key is used to link the two tables together.
We can stop here and continue in the next part of the series.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT