RelationalDBDesign RelationalDBDesign


Managing Constraints   «Prev  Next»

Adding Foreign Key Constraints - Exercise

Course project: Adding foreign keys

Objective: Add foreign key definitions to the table creation script

Exercise scoring

This exercise is worth a total of 24 points, 4 points for each correct insertion of the FOREIGN KEY command.When you complete your answer, submit it.

Background/overview

Earlier in this course, you created a script file with the SQL statements needed to create the tables in your COIN database, and subsequently added the PRIMARY KEY constraints to the script. Now you will modify those scripts to include foreign key constraints.

Download files

You can download a correct version of the script for this exercise, foreignkey.ssql, from the project download file found on the Resources page.

Instructions

Open the script file for creating tables you saved in the earlier module. Edit it to add foreign key constraints for the tables in the COIN database.

Hints

In the illustration shown above, the existence of a foreign key is implied by the relationship between the two tables. The lines between two tables indicate the parent with a single line and the child with a set of three lines.
The relationships between that tables are:
  1. The AUCTION table is the parent of the LOT table.
  2. The LOT table is the parent of the BID tables and COINS_IN_LOT table.
  3. The CLIENT table is the parent of the BID table and the COIN table.
  4. The COINS_IN_LOT table is the parent of the COIN table.
The owner_client_id and the bidder_client_id are both foreign keys of the client_id primary key in the CLIENT table.
Remember that the FOREIGN KEY constraint is defined on the child table in the relationship, and that a foreign key does not have to reference every column in its associated primary keys.

Submitting your exercise

When you are done, save the file with your changes. Copy the contents of the file into the text box below and submit your answer.