• 8 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 5/5/22

Link Tables Using Foreign Keys

 

In this chapter, you will learn how to model the associations you identified in the UML diagram. For example, linking a row in one table (e.g.,  production  ) to a row in another table (e.g.,  ProductionCompany  ).

By drawing your UML diagram, you have defined the associations between classes. For example, only one production company produces a production.

That’s a good place to start. But now, you need to be more precise by knowing which film is produced by which company. In other words, you must link each row of the  production  table with the rows in the   ProductionCompany  table.

Discover How Foreign Keys Come Into Play

The relational model allows you to do this using a foreign key.

A foreign key is an attribute (or set of attributes) in one table that refers back to the primary key of another table to model the link between the rows in them.

Here’s what it would look like if you were to model the link between two tables,  book  , and  person  :

The book table is linked to the person table using a foreign key
The book table is linked to the person table using a foreign key

The FK notation shows the attributes that make up a foreign key. You show a foreign key using a dotted line between two tables on a diagram as follows: 

Diagram showing the foreign key between the book and person tables
Diagram showing the foreign key between the book and person tables

The foreign key consists of only one attribute (  author_id  ) because the person primary key has only one attribute (  id  ). But if the  primary  key has two attributes, as is the case in your  location  table, you would need a foreign key with two attributes, which you could call  location  and  zip code  : 

Tabular representation of the location and shooting tables showing the primary and foreign keys as [PK] and [FK], respectively
Tabular representation of the location and shooting tables showing the primary and foreign keys as [PK] and [FK], respectively

Minimum Cardinality and Null Values

What Is a Null Value?

The relational model makes a practical value available, expressed as  null . This value is the equivalent of an empty cell in a spreadsheet where information is lacking or doesn't exist.

For example, if the person table holds attributes for  date_of_birth  and  date_of_death  , you could enforce a rule that the  date_of_birth  attribute must never be null if you want to record the date of birth for every person. This is known as the NOT NULL constraint. However, the date of death will be null for individuals still alive.

Understand How Null Values Are Used in Foreign Keys Based on Minimum Cardinality

When you convert UML associations into foreign keys, the NOT NULL constraint is helpful when the minimum cardinality is 1 (not 0).

Let’s take the example of the composition between  Film  (inherited from  production  ) and  ProductionCompany  . This relationship is one-to-many because the multiplicity is 1 at the  ProductionCompany  end, meaning that a production has one and only one production company. 

UML diagram showing the composition between Film (inherited from production) and ProductionCompany
UML diagram showing the composition between  Film  (inherited from   production  ) and  ProductionCompany

This association converts into a foreign key in the  production  table consisting of just one column:  production_company  , which refers to the primary key name in the  ProductionCompany  table:

Caption: The  production_company  foreign key references the  production_company  primary key
The  production_company  foreign key references the  production_company  primary key

If the minimum cardinality was 0, you could have a production with no production company, showing a null value in the  prod_company  column: 

Caption: Without the NOT NULL constraint
Without the NOT NULL constraint

However, since the minimum cardinality is 1, you must populate  production_company  for all rows and cannot have any null values. 

The NOT NULL constraint on the production_company column
The NOT NULL constraint on the production_company column

Let’s Recap!

  • A foreign key is a set of columns in one table that refers back to the primary key of another table to model the link between the rows in these two tables.

  • A foreign key can consist of one or more columns.

  • An absence of value is modeled using a null value in a table.

  • You can use null values in a foreign key to specify that a row in table A is not linked to any row in table B.

So there you have it! In the following chapter, you will learn to use primary and foreign key concepts to translate all the different association types you defined in your UML diagram.

Ever considered an OpenClassrooms diploma?
  • Up to 100% of your training program funded
  • Flexible start date
  • Career-focused projects
  • Individual mentoring
Find the training program and funding option that suits you best
Example of certificate of achievement
Example of certificate of achievement