RDBMS - Key Types in a Relational Database

Syllabus Detail

  • Types of keys; primary, composite and foreign, in a relational database. 12 ATAR

 

Background

  • The primary and foreign key concepts are fundamental to RDMBS
  • The primary keys within a database are used to define the relationships among the tables (records)
  • RDBM Systems rely on primary, foreign and composite keys to function

 

Primary Keys

  • A primary key is a column containing a unique field in each row
  • This unique value can be used to identify any record in the table
  • A primary keycannot be NULL, and should utilise an appropriate data type
  • A common example of a PK is a database with a table called Students
    • Each row in this table contains a StudentID field
    • This value is an integer, starting at 20200 and incrementing by one every entry
    • The 50th entry in the Students table would have a unique identifier (the corresponding StudentID) of 20250

 

Foreign Keys

  • A foreign key is a column (or multiple) containing the values of primary keys from other tables
  • Foreign keys are barely ever unique and unlike a primary key, may be set to NULL
  • Foreign keys are used to relate tables, as they establish a link between records
  • An example related to above would place the StudentID value from our Students table in to a foreign key field called StudentID in our Submissions table
    • For every submission entered in to the database, a value for StudentID is required
    • This value depicts which Student has submitted the work
    • The value corresponds directly to the StudentID in the Student table
    • The tables are now related to the primary and foreign keys, StudentID

 

Composite Keys

  • A composite key is a combination of two or more columns in a table
  • Whilst each field on its own may not be unique, when combined the value of the columns is unique
  • For example, if a database contained a table called Customers
    • A column for DOB exists
    • A column for FirstName exists
    • A column for LastName exists
  • A customer named Mary may not be unique, where as 01011970 Mary Smith can be seen as unique
  • A table's primary key can be made up of composite keys

 

Further Research

  • Read more about Primary and Foreign keys from Microsoft here
  • Read more about Composite Primary keys from SQLTeam here

 

Worksheet and Practice (yet to be added)

Found an error on this page? Please let us know via the contact form under the Home menu item.