What Is a Microsoft Access Primary Key?
Microsoft Access is part of the Microsoft Office productivity suite. Access is one of the few programs that allows a user to create and administrate a database with no knowledge of programming languages. The user interface is similar to those of other Microsoft Office programs. An administrator can build a database using point-and-click and wizard dialog boxes. It is important to maintain the integrity of the information. This can be done through the use of a primary key.
Other People Are Reading
-
Definition
-
Primary keys are in tables only because this is where the raw data is housed. Queries, forms, reports and macros are simply additional tools to manipulate the data in the table. A primary key is a table field or field(s) selected by Access or the user that identifies each record, is indexed and has a unique value for each record.
Creation
-
If the user does not identify the primary key field, Access will automatically generate a field and use it as the primary key. The field that is generated by Access will autonumber each record. If the primary key is assigned by the user, it is recommended that the field have unique values. For example, in an accounts receivable database, the invoice number field may be the primary key.
-
Use
-
Primary keys can be used to create relationships across tables. Using the accounts receivable database as an example, say there are two tables, one labeled "Customer Information" and the other "Invoices". In the Customer Information table are the customer account number, customer name and address. In the Invoices table are the customer account number and invoice number. The primary key in the Customer Information table is the customer account number field and the primary key in the Invoices table is the invoice number field. To run a query to include the invoice numbers and customer information, it is necessary to link the two tables in that query. The field used to link the tables together is the customer account number.
Caution
-
If a primary key is not used, or if the primary key field does not contain unique values, a query will return duplicate records.
Recommendation
-
Prior to the database creation, isolate the information that will be used as the primary key. Double-check the records to ensure that the values in the field are not duplicated. If text fields will be used as a primary key, make the naming conventions uniform. For example, if the name of a company is "ABC, Inc." in one record and "ABC, Incorporated" in another record, the database will treat it as two different companies.
-
References
Resources
- Photo Credit data security image by dinostock from Fotolia.com
