Know and understand characteristics of primary key and foreign keys

ICT 0417 – Databases: Primary and Foreign Keys

Objective

Know and understand the characteristics of primary keys (PK) and foreign keys (FK), how to choose appropriate data‑types, and how to use them to maintain data integrity throughout the design, form‑building and data‑manipulation stages of the database life‑cycle.

Why Keys Matter in the Systems Life‑Cycle

During the Design stage (Section 7 of the syllabus) the analyst creates a logical data model. Selecting suitable primary and foreign keys at this point ensures:

• Unambiguous identification of every record – entity‑integrity.
• Correct linking of related tables – referential‑integrity.
• Efficient implementation, easy maintenance and compliance with the IGCSE 0417 requirements for creating a database structure, forms and queries.

1. Primary Keys

Definition

A primary key (PK) is one or more fields whose combined values uniquely identify each record in a table.

Essential Characteristics

• Uniqueness – No two rows may share the same PK value.
• Non‑null – Every record must contain a value; NULL is not allowed.
• Immutability – The value should rarely (if ever) change.
• Single‑field or composite – A PK may consist of one column or a combination of columns.
• Appropriate data‑type – Use a compact, indexed type (e.g. INTEGER or BIGINT) for surrogate keys; short CHAR/VARCHAR for natural keys that are guaranteed unique.

Surrogate vs. Natural Keys

Composite Primary Key – Example

CREATE TABLE OrderDetails (
OrderID   INT NOT NULL,
ProductID INT NOT NULL,
Quantity  INT,
PRIMARY KEY (OrderID, ProductID)
);

The combination (OrderID, ProductID) ensures that the same product cannot appear twice on the same order.

Key Selection Checklist (Design Phase)

1. Identify candidate attributes.
2. Prefer a surrogate key (auto‑generated INTEGER) unless a natural attribute is guaranteed unique.
3. Confirm the attribute is stable, non‑null and as short as possible.
4. Decide whether a single‑field PK suffices; use a composite PK only when no single attribute is unique.
5. Document the rationale for future maintenance.

2. Foreign Keys

Definition

A foreign key (FK) is a field (or group of fields) in a child (referencing) table that points to the primary key of a parent (referenced) table, thereby establishing a relationship between the two tables.

Core Characteristics

• Referential integrity – The FK value must match an existing PK value in the parent table, or be NULL if the relationship is optional.
• Cardinality – Determines the type of relationship:

  • One‑to‑many (most common)
  • Many‑to‑many (implemented via a junction table containing two FKs)

• Optional nulls – Allowed when the relationship is not mandatory.
• Actions on UPDATE/DELETE – The DBMS can automatically propagate changes (CASCADE, SET NULL, RESTRICT, NO ACTION).

Foreign‑Key Actions (SQL syntax)

• ON UPDATE CASCADE – Change in the parent PK is automatically reflected in the child FK.
• ON DELETE CASCADE – Deleting a parent row also deletes all child rows that reference it.
• ON DELETE SET NULL – Deleting a parent row sets the child FK to NULL (used for optional links).
• ON DELETE RESTRICT – Prevents deletion of a parent row while child rows still reference it.

Example: Many‑to‑many relationship (Students ↔ Courses)

CREATE TABLE Students (
StudentID   INT PRIMARY KEY AUTO_INCREMENT,
FullName    VARCHAR(50) NOT NULL,
DateOfBirth DATE
);
CREATE TABLE Courses (
CourseCode  CHAR(6) PRIMARY KEY,
CourseName  VARCHAR(100) NOT NULL
);
CREATE TABLE Enrollments (
StudentID   INT,
CourseCode  CHAR(6),
EnrolDate   DATE,
PRIMARY KEY (StudentID, CourseCode),               -- composite PK
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (CourseCode) REFERENCES Courses(CourseCode)
ON UPDATE CASCADE ON DELETE SET NULL
);

3. Comparison of Primary and Foreign Keys

4. Entity‑Integrity vs. Referential‑Integrity

• Entity‑integrity – Rules governing primary keys: unique and never NULL.
• Referential‑integrity – Rules governing foreign keys: FK must correspond to an existing PK (or be NULL) and must obey defined update/delete actions.

5. Table‑Creation Checklist (IGCSE 0417 – 18.1)

1. Choose appropriate data‑types for each attribute (e.g. INT for IDs, CHAR/VARCHAR for codes, DATE for dates).
2. Define the primary key – decide surrogate vs. natural, single‑field or composite.
3. Define foreign keys – specify referenced table/column and desired ON UPDATE/ON DELETE actions.
4. Apply NOT NULL constraints where required (especially for PK columns).
5. Document the design – include rationale for each key choice.

6. Form‑Design Tips (IGCSE 0417 – 18.1 – Form Design)

When creating a data‑entry form that reflects the logical model, follow these guidelines:

• Map each table field to an appropriate control:

  • INT or BIGINT → numeric textbox (right‑aligned).
  • CHAR/VARCHAR → text box (max length set to field size).
  • DATE → date picker.
  • Foreign‑key fields → drop‑down list populated with the PK values from the parent table (e.g. StudentID list).

• Label every control clearly; use a consistent font size (e.g. 12 pt) and adequate spacing as required by the syllabus.
• Set the tab order to follow the logical flow of data entry.
• Include validation:

  • Required fields → NOT NULL enforcement.
  • Numeric ranges, date ranges, and format checks.

• Provide buttons for Save (INSERT), Update, Delete and Search to demonstrate manipulation of data (see Section 7).

7. Manipulating Data – Queries (IGCSE 0417 – 18.2)

Students must be able to retrieve, sort, search and calculate data using SQL SELECT statements.

Basic SELECT

SELECT StudentID, FullName
FROM Students
WHERE DateOfBirth > '2005-01-01';

Sorting (ORDER BY)

SELECT CourseCode, CourseName
FROM Courses
ORDER BY CourseName ASC;

Joining Tables (using FK relationships)

SELECT s.FullName, c.CourseName, e.EnrolDate
FROM Enrollments e
JOIN Students s   ON e.StudentID = s.StudentID
JOIN Courses c    ON e.CourseCode = c.CourseCode
WHERE e.EnrolDate BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY s.FullName;

Aggregations (calculations)

SELECT c.CourseCode, COUNT(e.StudentID) AS NumStudents
FROM Enrollments e
JOIN Courses c ON e.CourseCode = c.CourseCode
GROUP BY c.CourseCode
HAVING COUNT(e.StudentID) > 10;

8. Example Scenario – School Database

Tables

1. Students

   • StudentID – PK, INT AUTO_INCREMENT (surrogate)
   • FullName – VARCHAR(50)
   • DateOfBirth – DATE

2. Courses

   • CourseCode – PK, CHAR(6) (natural)
   • CourseName – VARCHAR(100)

3. Enrollments (junction table)

   • StudentID – FK → Students.StudentID
   • CourseCode – FK → Courses.CourseCode
   • EnrolDate – DATE
   • Composite PK = (StudentID, CourseCode)

Key benefits of this design:

• Entity‑integrity: each student and each course is uniquely identified.
• Referential‑integrity: every enrolment must refer to an existing student and an existing course.
• The composite PK prevents a student from being enrolled in the same course more than once.

9. Common Mistakes to Avoid

• Using a nullable or non‑unique column as a primary key.
• Choosing a primary key that may change (e.g., a surname).
• Creating a foreign key that references a non‑unique column.
• Omitting foreign‑key constraints – leads to orphaned records.
• Applying ON DELETE CASCADE indiscriminately – can cause unintended mass deletions.
• Leaving constraints disabled in the DBMS.
• Designing forms without matching controls for foreign‑key fields (e.g., using a free‑text box instead of a drop‑down).

10. Summary

Primary keys enforce entity‑integrity by uniquely identifying each record; foreign keys enforce referential‑integrity by linking related records across tables. Selecting appropriate data‑types, deciding between surrogate and natural keys, defining constraints correctly, and reflecting the logical model in both forms and queries are essential skills for the ICT 0417 IGCSE/A‑Level syllabus.