Online College Courses for Credit

+
1 Tutorials that teach Foreign Keys & Referential Data
Take your pick:
Foreign Keys & Referential Data

Foreign Keys & Referential Data

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify a set of queries written in the correct order that would accomplish the desired outcome.

(more)
See More
Tutorial

what's covered
This tutorial explores the use of foreign keys and the importance of the execution of SQL statements in two parts:

  1. Inserting with Foreign Keys
  2. Deleting with Foreign Keys

1. Inserting with Foreign Keys

When it comes to foreign keys, it can be a challenge to determine the order in which data must be inserted into tables especially when we have multiple tables that relate to one another. Given our database, we have the following foreign keys in place across the various tables:

File:11426-2230-1.png

In looking at the list, the first column shows the name of the foreign key constraint. The second and third columns show the child table that has a foreign key. The fourth and five columns show the parent table and primary key that the child is referencing.

The order in which the insert statements will need to occur assuming none of the records are referencing existing records would depend on tables that do not have foreign keys to other tables. If we look at our set of table constraints, we can identify that this would be the artist, genre, media_type, playlist, and the employee table:

File:11427-2230-2.png

Note that the employee table links to itself as the manager is linked using the reports_to column in the same table. So, the first five tables that would need to be inserted in would be:

artist
employee
genre
media_type
playlist

The order between the five would not matter in this case as the five tables are on the first level of tables. Next, we will want to look at the tables that reference those five tables.

File:11428-2230-3.png

On this level, we have the album, employee, customer, playlist_track, and track table. However, if we look at the second row, we see that the track table also has a foreign key to the album_id in the album table. This was not on the first set of tables so the track table cannot be added to yet. Likewise, in the last row, the playlist_track has a link to the track table which isn’t available yet. The next set of tables that can be inserted into next would be:

album
customer

Using the next set, we can identify that the following tables are linked to the ones at the prior level and do not have other dependencies:

File:11429-2230-4.png

Now, being that the album would have been inserted, the track table can be inserted into next. On top of that, the invoice table only depends on the customer so that can also be inserted into it. So, on the third level, we can insert into in any order:

File:11430-2230-5.png

Then on the final level, we can now insert into invoice_line and playlist_track as all dependencies have been accounted for.

For a review, we would have to insert into any grouping of the following levels of tables:

Level 1
artist
employee
genre
media_type
playlist

Level 2
album
customer

Level 3
track
invoice

Level 4
invoice_line
playlist_track

Using this structure, if we needed to insert an invoice on an existing customer, we would only have to look at the related tables on the levels below it. In this case, it would be the invoice and invoice_line.


2. Deleting with Foreign Keys

When it comes to deleting from tables, the order of the statements is very similar to the order of the inserting but backward. We want to delete the items at the lowest level first and work up. This is of course if there is no ON DELETE CASCADE set on the foreign keys in the tables. We must delete in the opposite direction as we would insert as we have to delete the child rows first before the parent rows. In essence, the tables would have to be deleted in the following order by level:

Level 4
invoice_line
playlist_track

Level 3
track
invoice

Level 2
album
customer

Level 1
artist
employee
genre
media_type
playlist

For example, if we wanted to delete a customer, we would have to delete from the invoice_line of the invoices that belong to that customer. Then delete the invoices in the invoice table that belong to the customer. Then finally deleting the customer.


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The foreign key constraints force us to insert and delete data from tables in a specific order.