3.0: Introduction
Last updated
Last updated
In Part 3, 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 1.9 on diagramming notation and Lesson 1.10 on foreign keys throughout this section.
Before diving into the challenges, we'll revisit the code you wrote back in Lesson 1 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.
The first 6 lines of code create the Product table. This code is almost identical to the code from Lesson 1.3.
Lines 7-12 create the Customer table. Again, this is almost identical to the code you wrote in Lesson 1.8.
The rest of the code is where we'll focus our attention for the remainder of this lesson. Lines 13-22 create the Purchase table that was built in Lesson 10. As previously mentioned, some of this code has been modified from what you had copied and pasted earlier so that the focus can be specifically on foreign keys.
To create a foreign key, you need to follow a certain format in T-SQL:
Section 1 (Line 18) - Begins with the word "CONSTRAINT," which is a keyword that tells SQL there are special rules for this table. The part of code that follows it FK_Purchase_Customer is the name of your foreign key. The specific name you give your foreign key is not important, but there are certain conventions that are usually followed. The standards according to the official Microsoft docs are outlined here:
In T-SQL, foreign keys start with "fk" or "FK", which stands for "foreign key".
This is followed by the referencing table name, which is underlined in red. Purchase is the table that is going to be referencing another table for data. You can see that Purchase is also boxed in red in Line 13 and the UML diagram. This is meant to show a clear connection between all these parts and how they work together.
This is followed by the referenced table name, which is underlined in yellow. Customer is the table where the data is stored.
Each section of the name is separated by an underscore ( _ ).
Section 2 (end of Line 18) - After stating the name of your foreign key, you'll type "FOREIGN KEY" into your query. Then, you'll type the name of the field within the referencing table that will act as the foreign key. In this example, (CustomerID) is the name of the field of the foreign key. The name of the field should be in parentheses and must match the existing field in the table. This same field is also boxed in the UML diagram to provide a more tangible view of the entities.
Section 3 (beginning of Line 19) - After identifying the foreign key field, type "REFERENCES" into your query. This is followed by the name of the referenced table, which is boxed in yellow. You can see that Customer also appears under the Navigation Properties in the Purchase table in the UML diagram, which indicates a connection to the Customer table. This connection is shown with a yellow arrow.
Section 4 (end of line 19) - The referenced table is followed by the field name in parentheses from the referenced table. In this example, the field name is CustomerID, which is boxed in green. Again, this should be identical to the existing field in the referenced table.
NOTE: The field names of the foreign keys in the referencing table and referenced table are not always the same! It is usually considered best practice for foreign key fields to have identical names across tables, so that convention is followed in this example. However, you will likely come across tables where this is not the case. In order to follow the structure of these tables, it will be important to recognize and understand which fields are linked together across tables.
Once you have a good understanding of the material presented in this lesson, please continue to Challenge 1.
