Selecting a Primary Key: When designing a table, identify a field that is guaranteed to be unique and never null. Numeric IDs (like Employee_ID) are generally preferred over text fields (like Name) because names can change or be shared by multiple people.
Establishing a Relationship: To link Table A to Table B, insert the Primary Key of Table A into Table B as a new field. In Table B, this field is designated as the Foreign Key.
Matching Data Types: For a relationship to function, the Foreign Key in the child table must have the exact same data type as the Primary Key in the parent table. For example, if the PK is a 4-digit integer, the FK must also be an integer.
| Feature | Primary Key (PK) | Foreign Key (FK) |
|---|---|---|
| Uniqueness | Must be unique for every record | Can have duplicate values |
| Nullability | Cannot be empty (Null) | Can be empty (depending on rules) |
| Purpose | Identifies a record | Links two tables together |
| Limit | Only one per table | Multiple per table allowed |
Identify the 'One-to-Many' Flow: In exam diagrams, look for the table where the ID repeats (the 'Many' side)—that is where the Foreign Key resides. The table where the ID is unique is the 'One' side containing the Primary Key.
Validate the Data Type: Always check if the suggested key field is appropriate for the data it holds. Using a 'Date' field as a Primary Key is risky because multiple events can happen on the same day; look for unique numeric identifiers instead.
Relationship Verification: When asked to link two tables, verify that the field names are consistent or clearly mapped. If Table A has ID and Table B has Owner_ID, make sure you explicitly state that Owner_ID references ID.
Confusing PK and FK: Students often think a field is a Primary Key just because it has 'ID' in the name. Remember: it is only a PK if it uniquely identifies the records in that specific table.
Redundancy Errors: A common mistake in database design is including non-key data (like a user's address) in multiple tables. Only the Key should be shared; all other attributes should remain in their home table.
Broken Links: If a record in the parent table is deleted but its ID remains in the child table as a Foreign Key, it creates an 'orphan record.' Modern databases use referential integrity constraints to prevent this.
