GMOD

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.

Documentation

Community

Tools