Using Primary Key Fields
The power in a relational database management system such as Visual FoxPro comes from its ability to quickly find and bring together information stored in separate tables. In order for Visual FoxPro to work most efficiently, each table in your database should include a field or set of fields that uniquely identifies each individual record stored in the table. This is often a unique identification number, such as an employee ID number or a serial number. In database terminology, this information is called the primary key of the table. Visual FoxPro uses primary key fields to quickly associate data from multiple tables and bring the data together for you.
If you already have a unique identifier for a table, such as a set of product numbers you've developed to identify the items in your stock, you can use that identifier as the table's primary key. But make sure the values in this field will always be different for each record — Visual FoxPro doesn't allow duplicate values in a primary key field. For example, don't use someone's name as a primary key, because names aren't unique. You could easily have two people with the same name in the same table.
When choosing primary key fields, keep these points in mind:
- Visual FoxPro doesn't allow duplicate or null values in a primary key field. For this reason, you shouldn't choose a primary key that could contain such values.
- You can use the value in the primary key field to look up records, so it shouldn't be too long to remember or type. You might want it to have a certain number of letters or digits, or be within a certain range of values.
- The size of the primary key affects the speed of operations in your database. When you create primary key fields, use the smallest size that will accommodate the values you need to store in the field.
Example:
The primary key of the Tasmanian Traders Products table contains product ID numbers. Because each product number identifies a different product, you don't want two products with the same number.
The Primary key for the Products table is the Product_id field.
.gif)
In some cases, you might want to use two or more fields that together provide the primary key of a table. For example, the Order_Line_Items table in the Tasmanian Traders database uses two fields as its primary key: Order_id and Product_id. In the next step, Identification of Relationships, you'll see why.