Be able to create and edit primary and foreign keys

Cambridge IGCSE ICT 0417 – Databases: Primary and Foreign Keys

1. Syllabus Coverage Map (Section 18)

2. How Primary & Foreign Keys Fit into the Systems Life‑Cycle (Section 7)

• Analysis: Identify the entities (e.g., Students, Enrolments) and the data that must uniquely identify each record.
• Design: Choose a primary key for every table, decide where foreign keys are required, and draw a relational schema.
• Development: Create tables, define primary‑key and foreign‑key constraints, and set appropriate referential‑integrity actions.
• Testing: Insert sample data; verify that duplicate primary‑key values are rejected and that foreign‑key violations are caught.
• Implementation: Deploy the database, back‑up before any structural change, and assign user privileges for creating or altering keys.
• Evaluation: Review the system for orphan records, performance of cascade actions, and whether the key design still meets users’ needs.

3. Safety & e‑Safety Considerations (Section 8)

• Maintaining referential integrity protects data from accidental loss or corruption – a core data‑security principle.
• Before changing a primary key, back up the database. Cascading updates can modify many related rows.
• Restrict the ability to create, alter, or drop key constraints to authorised users (e.g., DB‑admin role).
• When primary keys contain personal identifiers (e.g., National Insurance numbers), ensure compliance with data‑protection legislation such as GDPR.
• Document all key‑related changes in a change‑log to support audit trails and future troubleshooting.

4. Primary Key

4.1 Definition

A primary key (PK) is one or more fields that uniquely identify each record in a table. It enforces entity integrity.

4.2 Rules (AO3 – Evaluation)

• Every record must have a unique value.
• Values cannot be NULL (i.e., empty).
• Only one primary key per table, but it may be a composite key (multiple fields).
• Choose the simplest data type that will remain stable (e.g., INTEGER, CHAR(10)).
• If a natural key is used, ensure it will never change; otherwise prefer an artificial key (auto‑number).

4.3 Creating a Primary Key – User‑Interface Steps

1. Microsoft Access
   1. Open the table in Design View.
   2. Select the field (or fields) that will become the PK.
   3. Click the Primary Key button on the toolbar (or set the Primary Key property to “Yes”).
   4. Save the table (Ctrl + S).
2. MySQL Workbench / phpMyAdmin
   1. Open the table’s Alter Table dialog.
   2. Select the column(s) and tick the PK checkbox, or click “Add Primary Key”.
   3. Apply/Save the changes.
3. SQL (generic)

   CREATE TABLE Students (
       StudentID   INT NOT NULL AUTO_INCREMENT,
       FirstName   VARCHAR(30),
       LastName    VARCHAR(30),
       PRIMARY KEY (StudentID)
   );
           

4.4 Editing a Primary Key

1. Open the table in Design/Alter mode.
2. Remove the existing PK designation (clear the PK flag or drop the constraint).
3. Make the required changes – rename the field, change its data type, or add/remove columns.
4. Re‑assign the primary key to the new field(s). For a composite key, select all fields before setting the PK.
5. If other tables reference the old PK, add ON UPDATE CASCADE to those foreign‑key constraints **before** changing the PK, otherwise the DBMS will reject the alteration.
6. Save the table.

4.5 Example: Altering a Primary Key with Cascading Updates

-- 1. Drop the existing foreign‑key that points to the old PK (if any)
ALTER TABLE Enrollments
DROP FOREIGN KEY fk_enroll_student;

-- 2. Change the primary key definition
ALTER TABLE Students
DROP PRIMARY KEY,
ADD PRIMARY KEY (NewStudentID);

-- 3. Re‑create the foreign‑key with ON UPDATE CASCADE
ALTER TABLE Enrollments
ADD CONSTRAINT fk_enroll_student
FOREIGN KEY (StudentID)          -- matches the new PK column name
REFERENCES Students(NewStudentID)
ON UPDATE CASCADE
ON DELETE RESTRICT;

4.6 Composite Primary Key Examples

Any combination of fields that together guarantee uniqueness can serve as a PK.

-- Example 1: Student‑Course registration (two‑field PK)
CREATE TABLE CourseRegistrations (
    StudentID   INT NOT NULL,
    CourseCode  CHAR(6) NOT NULL,
    RegDate     DATE,
    PRIMARY KEY (StudentID, CourseCode)
);

-- Example 2: Order‑Line items (three‑field PK)
CREATE TABLE OrderLines (
    OrderID     INT NOT NULL,
    ProductCode CHAR(8) NOT NULL,
    LineNumber  SMALLINT NOT NULL,
    QtyOrdered  INT,
    PRIMARY KEY (OrderID, ProductCode, LineNumber)
);

5. Foreign Key

5.1 Definition

A foreign key (FK) is a field (or set of fields) in a *child* table that references the primary key of a *parent* table, establishing a relationship and enforcing referential integrity.

5.2 Rules

• The FK must have the **same data type, length, and signed/unsigned attributes** as the referenced PK.
• Duplicate FK values are allowed – they create a many‑to‑one relationship.
• NULL values are permitted when the relationship is optional.
• When a referenced PK value is changed or deleted, the database must know what to do: CASCADE, SET NULL, RESTRICT, or NO ACTION.

5.3 Creating a Foreign Key – UI Steps

1. Identify the parent table and its PK (e.g., Students.StudentID).
2. Add a matching field to the child table (e.g., Enrollments.StudentID) with the same data type.
3. Open the Relationships window (Access) or Alter Table dialog (MySQL).
4. Define the relationship:
   • Check Referential Integrity.
   • Select the appropriate Update/Delete Rule (e.g., ON UPDATE CASCADE).
5. Save the changes.

5.4 Creating a Foreign Key – SQL Example

ALTER TABLE Enrollments
ADD CONSTRAINT fk_enroll_student
FOREIGN KEY (StudentID)
REFERENCES Students (StudentID)
ON UPDATE CASCADE
ON DELETE RESTRICT;

Here ON UPDATE CASCADE automatically updates child rows if the parent StudentID changes; ON DELETE RESTRICT prevents deletion of a student while enrolments exist.

5.5 Editing a Foreign Key

1. Open the Relationships or Alter Table view.
2. Delete or disable the existing foreign‑key constraint.
3. Make the required field changes (rename, change data type, modify length, adjust signed/unsigned).
4. Re‑create the foreign‑key constraint, optionally changing ON DELETE / ON UPDATE actions.
5. Test the new constraint by inserting, updating, and deleting records.

5.6 Cascading Actions – When to Use Them

6. Common Errors & Troubleshooting Checklist

1. Duplicate primary‑key value – Verify uniqueness; consider AUTO_INCREMENT or IDENTITY columns.
2. NULL in primary‑key field – Change the column to NOT NULL or supply a default value.
3. Data‑type mismatch (FK vs PK) – Ensure identical type, length, and signed/unsigned attributes.
4. Foreign‑key violation on INSERT/UPDATE – Confirm the referenced parent value exists; insert NULL if the relationship is optional.
5. Cannot delete parent row – Review the FK’s delete rule; add ON DELETE CASCADE or delete dependent child rows first.
6. “Cannot alter table … because it is referenced by a foreign key” – Temporarily drop the FK constraint, make the change, then re‑create the constraint.

7. Summary Comparison

8. Quick Revision Questions

1. List the three essential rules that a primary key must satisfy.
2. Explain why a foreign key is allowed to contain duplicate values.
3. Describe the steps required to change a single‑field primary key into a composite primary key.
4. What error message appears if you try to insert a child record with a foreign‑key value that does not exist in the parent table, and how would you resolve it?
5. When would you choose ON DELETE SET NULL instead of ON DELETE CASCADE?

9. Practical Integration with Other ICT Sections (11‑21)