Key Pieces - An Introduction to the Primary and Foreign Key

Imagine a student who has taken a few semesters of a foreign language – they have covered the basics of reading, writing, and conversation.  They have learned about the culture of the lands that speak the foreign language.  Are they fluent?  No.  Could they pass as a native on the street?  Probably not.  However, they are building the fundamentals necessary for the culmination of their studies:  the semester abroad, expanding job duties, or maybe even starting a new life in a new country.  With the key pieces in place, they are set for a transformational experience.  


Similarly, there are essentials that a learner needs to cover as they enter into the field of data base management.  Learning the language is a first step that cannot be skimmed or skipped.


Keys truly are the key pieces in developing a comprehensive understanding of database basics.  Learning the types of keys in relational databases was akin to learning that Eskimos have 50 different ways of saying "snow" (read: overwhelming, confusing, and frustrating at times).  Start simple with the primary and foreign keys.  This logical relationship is at the core of relational databases.


The primary key is a constraint that uniquely identifies each record in a database table.  It is the parent to the foreign key, an identifier that has a dependent relationship to the primary key located in another table.  The dependency on the primary key constrains the data in the foreign "offspring" table, as there cannot be a record created in the foreign table without a correlating entry in the parent table.  There can, however, be a record in the parent table without a correlating record in the foreign table.

 
For example, the following parent table of Department, which is part of the DB2 sample database, has a list of department numbers in a fictitious company.  The department number or DeptNo column is the primary key, independent from other attributes in the table.  The Project table has a column of project numbers and these are primary keys in the Project (child) table.   While in the parent table DeptNo is a primary key, it is a foreign key in the child table.  

Key Example Pic.JPG

 

 

 

2 Comments
1 Like

Great Post - I'll enjoy following from the perspective of a newbie

July 25, 2014 11:50 PM by Troy Coleman

I enjoyed reading about RI from a newbie.  It helps all of us remember the fun we had in the beginning of our carreers.  Keep up the postings.  It will help you grow and will help others.

Great start!

July 30, 2014 08:19 AM by Mark Labby

Great start Lauren! 


Too often the articles on DB2 are by long time users who have a tendency to skip over minor details when explaining things because they do it intuitively or they assume 'everyone knows that'. It's good to get the perspective of someone just starting out who can help others in the same boat. That is the original spirit of IDUG and what makes the group strong. ;-)

Recent Stories
Introduction to Db2 for z/OS System Profiles

10 questions to review and understand your active log data set configuration

DB2 for z/OS query support over FTP