Understanding primary key

1. The primary key is one (or more) field that uniquely identifies every record in a table.
2. A primary key is an index of the table.

• When creating a table in Design View, Access can automatically add an AutoNumber field (typically named "ID") and set it as the primary key.
• This is done by default when you create a new table in Datasheet View or use the Table Wizard, where Access assigns an **AutoNumber field** and designates it as the primary key.
• The AutoNumber field generates unique, sequential numbers automatically, ensuring a unique identifier for each record.

• In Design View, you can define a primary key by selecting an existing field (or combination of fields) that contains unique values.
• Steps:
  • Open the table in Design View.
  • Select the field (or multiple fields for a composite key) you want to set as the primary key.
  • Click the "Primary Key" button in the ribbon (under the "Table Tools" contextual tab) or right-click the field and choose "Primary Key."
  • The field must contain unique values and cannot allow nulls.

• If no single field can uniquely identify a record, you can create a composite (or compound) primary key using multiple fields.
• Steps:
  • In Design View, hold down the Ctrl key and select the fields you want to include in the composite key.
  • Click the "Primary Key" button in the ribbon.
  • The combination of values in these fields must be unique for each record.
• Example: In a table tracking orders, you might use a combination of OrderID and CustomerID as a composite primary key.

• You can define a primary key using a SQL statement when creating or altering a table.
• Example (creating a table with a primary key):

CREATE TABLE Employees (
    EmployeeID INTEGER PRIMARY KEY,
    FirstName TEXT,
    LastName TEXT
);

ALTER TABLE Employees
ADD CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID);

• When creating a table using the Table Wizard, Access prompts you to choose whether to let Access create an AutoNumber primary key or to select your own field(s) as the primary key.
• The wizard guides you through selecting fields and designating one (or more) as the primary key, similar to the manual process in Design View.

• When importing data from an external source (e.g., Excel, CSV, or another database), Access can automatically designate a primary key if the imported data includes a field with unique values.
• During the import process, Access may prompt you to select an existing field as the primary key or add an AutoNumber field for this purpose.

• Field Requirements for Primary Keys: A primary key field (or combination of fields) must contain unique values and cannot contain null values. AutoNumber fields are ideal because they automatically enforce uniqueness and non-null constraints.
• Composite Keys: Use these when a single field isn’t sufficient to ensure uniqueness, but be cautious as they can complicate queries and relationships.
• Modifying Primary Keys: You can change or remove a primary key in Design View by selecting the field(s) and toggling the "Primary Key" option, provided the new configuration meets the uniqueness and non-null requirements.
• No Other Direct Methods: Access 365 doesn’t offer additional distinct methods beyond these. For example, there’s no programmatic way to set a primary key via macros or VBA without using SQL or manipulating table design.

1. The SSN field is going to be the primary key for this table. The first step is to select the field by clicking the gray selection box to the left of the field.
2. The field is now selected. If you were creating a multiple field key you would now select any additional fields by holding down the Shift key while clicking the selection box for the additional fields. To define the selected key as the primary key click the Primary Key button on the toolbar, it is in the middle, and looks like a key.
3. The key symbol in the selection box indicates that the field is the primary key for the table. When the field is selected in Design view the Primary Key button on the toolbar is depressed. If you need to remove the primary key from the table, you can select the primary key field(s) and click the Primary Key button on the toolbar and it disappears.
4. This is the end of the simulation. To close this window and return to your lesson, click on the End Simulation button.