The Primary Key
A primary key is a field or set of fields that uniquely identify each row in the table. For example, only one row of your ‘areas’ table should have the ‘area_easting’ and ‘area_northing’ values of 343 and 78. If you were to accidentally enter this combined area identification into two different rows of the table, then how would you be able to know which row recorded the proper data for the excavated square at the coordinates of 343, 78? In the real-world, of course, there can only be one square excavated at that location, so your database should also restrict your ability to only create a row to represent that entity only once. Any time you want to work with the data from area 343.78, you should first search the database for that row and then modify and add data to the existing row. A table can have only one primary key, which will be the main way for uniquely identifying rows of data in that table.
Foreign keys are used when relating tables to each other. One table will reference a field or set of fields in a second table, and those fields are considered the foreign key from the perspective of the first table. In other words, the fields of the foreign key are contained within both tables, but only the referencing table stores the foreign key itself. In our 'contexts' table, for example, we included the 'area_easting' and 'area_northing' fields. By linking these two fields to the same two fields in the 'areas' table, we are creating a foreign key in the 'contexts' table. In this way we restrict that a new context can only be added to an existing area. We will cover foreign keys more in our section on related tables.
An index enables the computer to find information in the table. Think of the index in the back of a book. Rather than searching through an entire book to find a word, you can more quickly search through the alphabetical listing in the few pages in the back of the book that contain the index. Then you are able to use the page number provided to jump to that specific part of the book and get the information you need. Keys function as indexes as well as constraints.
In the following screencast, we use Pgadmin to set the primary key for the areas table in PostgreSQL. Note that to edit a table, you right mouse click on that table and select the 'Properties...' option. Keys and other constraints are set in the 'Constraints' tab, and there is a 'Primary Key' subtab, where we click the + button to the right to add the primary key. There is a small edit box (looks like a box with a pencil) on the row's left side, which opens up a panel for configuring the primary key. You may name the primary key whatever you like, and in the 'definition' tab you can select the columns to include in the primary key.
Here we should make a specific note about using multiple fields in a primary key, which is known as a composite key. It is possible to use a single field, and some database professionals will even create an arbitrary number field as their primary key, regardless of the rest of the data in the table. Online you can find several websites that get into the details on the pros and cons of using a single or composite field key. In some cases, computer programming is simpler if your database uses single field keys. For archaeology, however, our overriding concern is keeping the data clean by preventing duplicates. Therefore, we should avoid at all times adding extraneous fields that have no inherent meaning. These types of fields can easily become corrupt, for example, if an area gets duplicated in your database accidentally. Archaeologists need their identity fields (like the two fields that uniquely identify an area) to be correct and well-maintained at all times. Thus, as archaeologists, it is better for us to depend only on these meaningful fields as unique composite keys.
Exercise: Create Primary Keys in all Your Tables
Return to the three tables you created in the last section. Create primary keys for each table. Remember that the contexts table's primary key will include 3 fields, and the samples table will also be a composite key.This page has paths:
- Relational Databases for Archaeology Peter Cobb