Databases: Primary, Foreign and Alternate Keys

This introduces some terminology frequently used in relations databases.

In a database table, certain columns are keys. Keys help identify rows / records and link records together.

Primary Key

Uniquely identifies a record/row/object/person.

Alternate Key

Also uniquely identifies a record/row/object/person

Foreign Key

Related tables are linked together by taking the primary key from one table and placing in the related table. The primary key then becomes a foreign key.

In this case, if we had another table that described Institutions', then the Institution column in the Instructors table would be a foreign key.

Example

The Instructors table, again.

Instructor Table

Primary Key	Foreign Key?	Alternate Key
Participant Affiliation Email Scott Cain Ontario Institute for Cancer Research scott@scottcain.net Tim Carver Sanger Institute tjc@sanger.ac.uk Dave Clements NESCent clements@nescent.org Stephen Ficklin Clemson University ficklin@clemson.edu Syed Haider EBI syed@ebi.ac.uk Ian Holmes UC Berkeley ihh@berkeley.edu Carson Holt University of Utah carson.holt@utah.ed Robin Houston Sanger Institute rh11@sanger.ac.uk Ed Lee Lawrence Berkeley National Lab elee@berkeleybop.org Sheldon McKay Cold Spring Harbor Laboratory mckays@cshl.edu Joshua Orvis Institute for Genome Sciences jorvis@som.umaryland.edu Mitch Skinner UC Berkeley mitch_skinner@berkeley.edu Giles Velarde Wellcome Trust Sanger Institute gv1@sanger.ac.uk Junjun Zhang Ontario Institute for Cancer Research Junjun.Zhang@oicr.on.ca

Note

This simplified table contains a number of bad practices, such as the primary and foreign key values used, and naming conventions. We'll get to those.

Back to Chado Tutorial.