Be able to create and edit primary and foreign keys

Topic 18 – Databases

Objective

Be able to create and edit primary and foreign keys.

1. What is a Primary Key?

A primary key is a field (or combination of fields) that uniquely identifies each record in a table.

• Must contain a unique value for every record.
• Cannot contain NULL (empty) values.
• Only one primary key per table, but it may be composed of multiple fields (composite key).

2. Creating a Primary Key

Steps to create a primary key in most database software (e.g., Microsoft Access, MySQL Workbench):

1. Open the table in Design/View mode.
2. Select the field that will serve as the primary key.
3. Set the field property Primary Key (often a button or checkbox).
4. Save the table structure.

3. Editing a Primary Key

When a primary key needs to be changed:

1. Open the table in Design mode.
2. Remove the existing primary key designation (clear the Primary Key property).
3. Make any required changes to the field(s) (e.g., change data type, rename).
4. Select the new field(s) and set them as the primary key.
5. Save the table. The database will enforce uniqueness on the new key.

4. What is a Foreign Key?

A foreign key is a field (or combination of fields) in one table that refers to the primary key of another table, establishing a relationship between the two tables.

• Ensures referential integrity – records in the child table must correspond to existing records in the parent table.
• Can contain duplicate values (many‑to‑one relationship).
• May allow NULL if the relationship is optional.

5. Creating a Foreign Key

Typical steps (illustrated for a relational database management system):

1. Identify the parent table and its primary key.
2. In the child table, add a field with the same data type as the parent primary key.
3. Open the table design view and select the field.
4. Define a Relationship (often via a Relationships window or ALTER TABLE statement):

   ALTER TABLE ChildTable
   ADD CONSTRAINT fk_child_parent
   FOREIGN KEY (ParentKeyField)
   REFERENCES ParentTable (PrimaryKeyField);
               

5. Save the changes. The database now enforces the foreign‑key rule.

6. Editing a Foreign Key

To modify an existing foreign key:

1. Open the Relationships or Table Design view.
2. Delete or disable the current foreign‑key constraint.
3. Make required changes to the field (e.g., rename, change data type).
4. Re‑create the foreign‑key constraint using the updated field.
5. Test by inserting or updating records to confirm referential integrity.

7. Common Errors and How to Fix Them

• Duplicate values in a primary key field: Ensure each record has a unique value; consider using an auto‑number field.
• NULL values in a primary key: Change the field to NOT NULL or provide a default value.
• Data type mismatch between foreign key and referenced primary key: Align data types (e.g., both INTEGER).
• Attempting to delete a parent record that is referenced: Use cascade delete rules or remove dependent child records first.

8. Summary Table

Aspect	Primary Key	Foreign Key
Purpose	Uniquely identifies each record in its own table.	Links a record to a primary key in another table.
Uniqueness	Must be unique.	May contain duplicate values.
NULL values	Not allowed.	Allowed if relationship is optional.
Number per table	One (can be composite).	Zero, one, or many.
Enforces	Entity integrity.	Referential integrity.

9. Suggested Diagram

10. Quick Revision Questions

1. What are the three essential rules for a primary key?
2. Explain why a foreign key can contain duplicate values.
3. Describe the steps to change a primary key from a single field to a composite key.
4. What happens if you try to insert a record into a child table with a foreign‑key value that does not exist in the parent table?