4.0: Introduction

In Part 4, you will be given several challenges in which you will have to write a single SQL script to build the given tables. It might be helpful to refer to Lesson 2.9 on diagramming notation and Lesson 2.10 on foreign keys throughout this section.

Before diving into the challenges, we'll revisit the code you wrote back in Lesson 2 for the Product, Customer, and Purchase tables. The code below has been slightly modified from what was written in the previous lesson so that you can focus on the most important concepts for the challenges ahead.

PostgreSQL Foreign Key Explanation

UML Customer-Purchase-Product Tables

• The first 6 lines of code create the Product table. This code is almost identical to the code from Lesson 2.3.

• Lines 7-12 create the Customer table. Again, this is almost identical to the code you wrote in Lesson 2.8.

• The rest of the code is where we'll focus our attention for the remainder of this lesson. Lines 13-18 create the Purchase table that was built in Lesson 10. As previously mentioned, this code has been modified from what you had copied and pasted earlier so that the focus can be specifically on foreign keys.

  • CustomerID (underlined in blue, beginning of Line 16) - This is a foreign key in the Purchase table, which has been boxed in red in Line 13.

  • Customer (boxed in yellow) - This is the name of the referenced table. The referenced table is where the referencing table will look to get data. In this example, Purchase is the referencing table. The REFERENCES keyword in Line 16 show this relationship.

  • CustomerID (underlined in green, end of Line 16) - This is the specific column or field of the referenced table that is being linked to the foreign key. It must be in parentheses. By default, PostgreSQL will assume that the primary key of the referenced table is the foreign key. If you are using this default setting, as is being done in this example, then this part of the script is optional. In this case, it has been included for clarity.

PostgreSQL Navigation Menu

Note: This view has been truncated to show only relevant portions of the navigation menu. After running the script above and refreshing the navigation menu, you can expand the "Constraints" tabs to view the existing keys. The key names were generated by PostgreSQL and color-coded to correspond to the other diagrams above. This is meant to demonstrate the relationship between these entities.

Once you have a good understanding of the material presented in this lesson, please continue to Challenge 1.