How to Define a Foreign Key in a Physical Model

A foreign key is one of the fundamental concepts of relational databases. You don’t store all your data in one table, but many different tables. Nonetheless, all your data is related. That’s where the foreign key comes into play. It facilitates the process of linking the tables. Read on to find out more.

This article focuses on the concept of the foreign key in a physical model. First, we’ll briefly go over foreign key basics. Next, we’ll dive into the details of defining a foreign key in a physical model of the database. We’ll discuss the difference between candidate keys, primary keys, and alternate keys so you can understand which one to use as a reference column. You’ll also see how vital the foreign key concept is for joining the tables. Finally, I’ll show you how to fetch the SQL code of the physical model from Vertabelo.

Let’s get started.

Foreign Key Basics

A foreign key creates a link between two tables. This link is based upon a column shared by these tables. Look at the picture below:

The two tables presented above store information about books and authors. The line between them signifies that one author can have one or more books, but one book can have only one author.

The Books and Authors tables share the AuthorId column. In the Authors table, which is a primary table, the AuthorId column is a primary key (PK). In the Books table (a foreign table), the AuthorId column is a foreign key (FK).

Let’s look at some data.

The Books table assigns an author to each book using the AuthorId column. You can trace the AuthorId column to the Authors table to fetch information about the author. Please note that the AuthorId column in Books can use only the values present in the AuthorId column of Authors, as Authors is the primary table.

The information about authors is stored in the Authors table, not in the Books table. This way, there is no duplicated data. Otherwise, our data would all be stored in one table, like this:

We aim to avoid data duplication. Foreign keys let us divide data into tables and then link these tables.

To summarize, the foreign key constraint in a physical model joins the tables on a shared column(s). Here, this shared column is the AuthorId column.

Now we’re ready to move on to defining a foreign key in a physical model.

The Foreign Key in a Physical Model

Vertabelo enables us to create ER diagrams easily. ERDs include all the information necessary to construct a database; as such, they let us define foreign keys.

Let’s see how to define a foreign key in a physical model.

Defining a Foreign Key

We’ll start by defining our Books and Authors tables with their respective primary key columns.

First, we need to switch the cursor from (1) Select to (4) Add new reference.

Let’s add a new reference by dragging the line from our primary table (Authors) to our foreign table (Books).

We can now customize our new reference and add the remaining columns to our tables.

We’ve modified the name of the foreign key column in the Books table to be the same as in the Authors table. Also, we added the remaining columns to each table. Furthermore, the cardinality was adjusted to emphasize that one author can have one or more books.

Let’s take a closer look at the Reference Properties section.

By default, the name of our reference is Books_Authors, but you can change it into anything you want. The cardinality defines the link specification. Here, it says that one row from the primary table can be assigned to one or more (that is, 1..*) rows of the foreign table. As mentioned before, one author can have one or more books, but one book can have only one author.

Later, there is a block that stores primary and foreign tables’ information. We see that our primary table is Authors, and our foreign table is Books. These tables share the AuthorId column, which is a reference column. Later on, we’ll see how to modify the reference column(s).

Lastly, the two drop-downs at the bottom let you select the action on update/delete of the primary table’s rows. (More on that later.) You can also add multiple references between the two tables.

Primary Key, Alternate Key, or Candidate Key?

Why does this matter? Because the primary table column that’s used as a reference doesn’t always need to be that table’s primary key column.

We could choose a column or their combination from the set of candidate key columns. The candidate key columns fulfill the requirements to be the primary key. Let’s visualize it:

What the picture above says is candidate keys = primary key + alternate keys. The set of candidate keys contains all the columns that could well be the primary key. You can get more insight on candidate keys in this article.

So how do we choose a primary key – especially if all the candidate keys could fill this role? Plus, there are different types of primary keys, such as surrogate keys or natural keys. In this article, you can learn how to choose a good primary key.

You can also create a reference as an alternate key if you don’t want to use the PK as the reference column between tables.

Foreign Key Columns

There is an easy way to modify reference columns.

In Vertabelo, we can add more reference columns by choosing the columns from the drop-down menus for the primary and foreign tables and clicking the Add button next to it.

We can also remove the reference columns by clicking the x button next to the already-added ones.

Actions on Update and Delete

In Vertabelo, the Reference Properties section lets us define the specific actions on when we update/delete a row from the primary table. Let’s look at the possibilities:

• RESTRICT:
  By choosing this option, you restrict yourself from updating/deleting the primary table’s rows.
• CASCADE:
  Every update/delete action made to the primary table’s AuthorId column results in the same action to the foreign table’s AuthorId
• SET NULL:
  Every update/delete action made to the primary table’s AuthorId column results in the foreign table’s AuthorId column value(s) set to NULL.
• SET DEFAULT:
  Every update/delete action made to the primary table’s AuthorId column results in the foreign table’s AuthorId column value(s) set to its default value.

Getting Your SQL Code

After creating your ER diagram in Vertabelo, you can grab the SQL codes straight from there and save yourself additional work!

To do so, click the Generate SQL Script button from the toolbar.

Every update/delete action made to the primary table’s AuthorId column results in the foreign table’s AuthorId column value(s) set to its default value.

You can choose whether to generate a SQL script to create or remove the objects. You can also select which elements are included in the SQL script.

Once you click the Generate button, the window is extended. You can choose a file name and save the file in Vertabelo (click the Save button) or download it to your local environment (click the Download button).

Now your SQL script is ready to run!

First, we create the Books and Authors tables using the CREATE TABLE statement. After that, we add the foreign key constraint into the Books table. To do so, we use the ALTER TABLE statement. This constraint’s name is Books_Authors. It links the Books and Authors tables using the AuthorId column.

Foreign Key: A Relational Database Essential

A foreign key is a foreign column that comes from another table. It gives you the means to relate data stored in different tables. Now you can divide and conquer your data! But it is still possible to put it all back together, thanks to the references created between the tables.

Check out our video tutorial about references between the tables. And don’t forget to practice on your own.

Good luck!