Objective 1.2 Manage relationships and keys
- 1.2.1 Create and modify relationships
- 1.2.2 Set key fields
OBJECTIVE 1.2: Contents
1.2.2 Set key fields
Set key fields
In Access, key fields are used when you establish table relationships. For example, if you have a table named Project Managers, the table could include the ProjectManagerID field as its primary key. A table’s primary key uniquely identifies each record in the table. You can then add the ProjectManagerID field to the Projects table to create a relationship between the tables that lets you identify the manager for each project. In the Projects table, the ProjectManagerID field is referred to as a foreign key. Primary keys and foreign keys can also be used in queries to join tables; Access uses that relationship to retrieve the set of records that match the criteria you define.
You can use a single field (for example, a unique product or customer code, or an ID field that is set to the AutoNumber data type that Access provides) or a combination of fields as a table’s primary key. A multifield primary key is called a composite key. For an AutoNumber field, Access assigns a unique number to each record in a table; you don’t need to keep track of values that might be duplicates. If you don’t use the AutoNumber data type but instead use a field whose value you enter, be sure that you set the field’s Required property to Yes and that you use a field or a combination of fields whose values change infrequently or not at all.
See Also: For information about setting field properties, see “Objective 2.4: Create and modify fields.”
Tip: When you create a new table, Access includes an ID field in the table and sets this field to be the table’s primary key.
A foreign key field should be set to the Number data type. You do not need to enter data for the foreign key field. Instead, the field’s values are tied to the unique values from the table in which the field is the primary key field.
To set the primary key for a table, you must have the table open in Design view. Access adds a small key icon to the row selector area to indicate that a field is a primary key field.
A key icon identifies the primary key field
If a table already contains data, the field or fields you designate for the primary key must have unique values. Also, if a primary key field is part of any table relationship, you must remove the relationship before you can change the primary key.
See Also: For information about adding and deleting relationships, see “1.2.1 Create and modify relationships” earlier in this topic.
To open a table in Design view
If the table is closed, right-click the table in the Navigation Pane, and then click Design View.
If the table is open in another view, on the Design tool tab, in the Views group, click View, and then click Design View.
See Also: For more information about object views, see “1.3.5 Change object views” in “Objective 1.3: Navigate through a database.”
To set a primary key
1. Open the table in Design view.
2. Select the field or fields you want to designate as the table’s primary key. To select multiple fields, press Ctrl and select the fields.
3. On the Design tool tab, in the Tools group, click Primary Key.
To remove the primary key designation from a field
1. Open the table in Design view.
2. Select the field or fields from which you want to remove the primary key designation.
3. On the Design tool tab, in the Tools group, click Primary Key.
To set a foreign key in a table
1. Open the table in Design view.
2. In the Field Name column, enter the name of the foreign key field.
3. In the Data Type column, select Number.
4. Save the changes to the table.