Add-set-change or remove the primary key Access 2007

Author: mety Labels::



What is a primary key?

A primary key is a field or set of fields in your table that provide Microsoft Office Access 2007 with a unique identifier for every row. In a relational database, such as Office Access 2007, you divide your information into separate, subject based tables. You then use table relationships and primary keys to tell Access how to bring the information back together again. Access uses primary key fields to quickly associate data from multiple tables and combine that data in a meaningful way.
This works because once you have the primary key defined, you can use it in other tables to refer back to the table with the primary key. For example, a Customer ID field in the Customers table might also appear in the Orders table. In the Customers table, it is the primary key. In the Orders table it is called a foreign key. A foreign key, simply stated, is another table's primary key.
Primary key and foreign key
Callout 1 Primary key
Callout 2 Foreign key

Often, a unique identification number, such as an ID number or a serial number or code, serves as a primary key in a table. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key.
A good candidate for a primary key has several characteristics. First, it uniquely identifies each row. Second, it is never empty or null — it always contains a value. Third, it rarely (ideally, never) changes. Access uses primary key fields to quickly bring together data from multiple tables.
An example of a poor choice for a primary key would be a name or address. Both contain information that might change over time.
You should always specify a primary key for a table. Access automatically creates an index for the primary key, which helps speed up queries and other operations. Access also ensures that every record has a value in the primary key field, and that it is always unique.
When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it a field name of "ID" and the AutoNumber data type. The field is hidden by default in Datasheet View, but you can see the field if you switch to Design View.
If you don't have in mind a field or set of fields that might make a good primary key, consider using a column that has the AutoNumber data type. Such an identifier is factless — it contains no factual information describing the row that it represents. It is a good practice to use factless identifiers because their values do not change. A primary key that contains facts about a row — a telephone number or a customer name, for example — is more likely to change, because the factual information itself might change.

Image showing Products table with primary key field.
Callout 1 A column with the AutoNumber data type often makes a good primary key, because it ensures that no two Product IDs are the same.


In some cases, you may want to use two or more fields that, together, provide the primary key of a table. For example, an Order Details table that stores line items for orders would use two columns in its primary key: Order ID and Product ID. When a primary key employs more than one column, it is also called a composite key.

Add an AutoNumber primary key

When you create a new table in Datasheet view, Access automatically creates a primary key for you and assigns it the AutoNumber data type. If you have an existing table to which you want to add a primary key field, however, you must open the table in Design view.
  1. Click the Microsoft Office Button Button image, and then click Open.
  2. In the Open dialog box, select and open the database.
  3. In the Navigation Pane, right click the table to which you want to add the primary key and, on the shortcut menu, click Design View.
  4. Locate the first available empty row in the table design grid.
  5. In the Field Name column, type a name, such as CustomerID.
  6. In the Data Type column, click the drop-down arrow and click AutoNumber.
  7. Under Field Properties, in New Values, click Increment to use incremental numeric values for the primary key, or click Random to use random numbers.

Set the primary key

If you have a table in which every record has a unique identification number, such as an ID number or a serial number or code, that field might make a good primary key. For a primary key to work well, the field must uniquely identify each row, never contain an empty or null value, and rarely (ideally, never) change.
To explicitly set the primary key, you must use Design view.
  1. Click the Microsoft Office Button Button image, and then click Open.
  2. In the Open dialog box, select and open the database.
  3. In the Navigation Pane, right click the table in which you want to set the primary key and, on the shortcut menu, click Design View.
  4. Select the field or fields you want to use as the primary key. To select one field, click the row selector for the field you want.
    To select more than one field, hold down CTRL and then click the row selector for each field.
  5. On the Design tab, in the Tools group, click Primary Key. Ribbon Design Tab Tools Group
    A key indicator is added to the left of the field or fields that you specify as the primary key.

Remove the primary key

When you remove the primary key, the field or fields that previously served as primary key will no longer provide the primary means of identifying a record. Removing the primary key does not delete the field or fields from your table, however. Rather, it removes the primary key designation from those fields.
Removing the primary key also removes the index that was created for the primary key.
  1. Click the Microsoft Office Button Button image, and then click Open.
  2. In the Open dialog box, select and open the database.
  3. Before you can remove a primary key, you must ensure that it doesn't participate in any table relationships. If you try to remove a primary key for which relationships exist, Access warns you that you must delete the relationship first.

    Delete a table relationship

    1. If the tables that participate in the table relationship are open, close them. You cannot delete a table relationship between open tables.
    2. On the Database Tools tab, in the Show/Hide group, click Relationships. Access Ribbon Image
    3. If the tables that participate in the table relationship are not visible, on the Design tab, in the Relationships group, click Show Table. Then select the tables to add in the Show Table dialog box, click Add, and then click Close.
    4. Click the table relationship line for the table relationship that you want to delete (the line becomes bold when it is selected), and then press the DELETE key.
    5. On the Design tab, in the Relationships group, click Close. Ribbon Design Tab Relationships Group
  4. In the Navigation Pane, right click the table in which you want to remove the primary key and, on the shortcut menu, click Design View.
  5. Click the row selector for the current primary key. If the primary key consists of a single field, click the row selector for that field.
    If the primary key consists of multiple fields, click the row selector for any field in the primary key.
  6. On the Design tab, in the Tools group, click Primary Key. Ribbon Design Tab Tools Group
    The key indicator is removed from the field or fields that you previously specified as the primary key.
Note When you save a new table without setting a primary key, Access prompts you to create one. If you choose Yes, an ID field is created that uses the AutoNumber data type to provide a unique value for each record. If your table already includes a AutoNumber field, Access uses it as the primary key.

Change the primary key

If you decide to change a table's primary key, you can do so by following these steps:
  1. Remove the existing primary key
  2. Set the primary key





0 comments |
Subscribe to: Post Comments (Atom)

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2