Primary, foreign and secondary keys
Introduction
Databases use primary keys, foreign keys and secondary keys. These are explained in detail below.
Primary Keys
Each record in each table must have one field that is unique, to ensure that each record can be differentiated from the others, even if the other fields are the same. You all have a unique student number, a unique NI number, you may have a tax number, a driving licence number, a club membership number and so on. These numbers are always different for each record, even if e.g. someone else has the same name as you.
-
- Every entity must have one attribute that is also known as the primary key. If an entity of records does not have a primary key attribute, then you must add one!
- A primary key is used to either find one record, or is used to sort a file in primary key order.
- The primary key, usually an ID number of some kind, is unique, for each record. You cannot have two records with the same primary key value.
- Many primary keys are made up of just one attribute. There are lots of circumstances, however, when the primary key is made up of 2, 3 or even more attributes. It is then known as a compound primary key. If you have normalised a database to 3NF, you will probably have seen an entity with a compound primary key.
Foreign Keys
Relational databases have more than one table. Records from each of the tables are combined to form the complete record of someone or something. Foreign keys are used to link the different records in different tables, so the database software knows which record in one table belongs to which record in another table.
-
- Foreign keys are used to link entities.
- A foreign key in one table is a primary key in another table.
- Although primary keys cannot have duplicate values in a table, foreign keys most definitely can. You have a situation where the same attribute cannot be duplicated in one table but can be duplicated in another!
- When you have a one-to-many relationship between two entities, you will need to link them using a foreign key. To do this, always copy the primary key from the entity on the 'one' side of the relationship and put it in the table on the 'many' side. In the table on the 'one' side, it is known as a ‘primary key’. In the table on the 'many' side, it is known as a ‘foreign key’.
Secondary keys
Very often, you may want to access a database by an attribute other than the primary key. Consider a textbook. Most of the time, a reader will want to access a particular topic in a book, so there exists a main contents page to allow them to do this. Sometimes, however, they want to look up specific things, so an index at the back of the book is provided. And sometimes, however, the reader may be more interested in looking at all the pictures! To enable this to happen, another index is provided - an index of pictures and where to find them! In this example, if a reader wanted to look at all the pictures, they could still go to the contents page and use that if they wanted to but it would take a long time, especially if it was a thick book. Now they have a secondary index file they can get back just the pictures whenever they want - and quickly. Secondary keys, then, enable a user to access data in an order other than the primary key order and they allow a user to filter what data to display. New secondary indexed files are created because a different set of information is required (possibly in a different order) from the main set of records frequently enough to justify doing it. If a particular set of information were needed once in ten years, it wouldn't be worth the overheads of setting one up.
Another example of the use of secondary keys
For example, imagine a bus company that stores bus details. It might have in a typical record the bus code, departure and arrival points, times of arrival and departure, number of seats in the bus and drop-off points. You could easily get a list of all the buses, listed by the primary key (the bus code) from the main file. But when someone goes to the bus company to buy a ticket, they just want to know only when the buses leave from their town to London. Now this is a question that the bus company might get asked one hundred times a day! They could run a query. They would need to set one up and then check the record of every bus. If they had 20000 buses, checking each of them would be time consuming, especially if they had to do this so many times every single day! So instead, they set up a secondary index file, which only has buses from the hometown to London and the time of departure. It will be ordered according to a secondary key that has been set up. The result of using a secondary index file with a secondary key is faster access to the information the customer wants!