Foreign keys enforce referential integrity: they ensure that each row has a valid parent row (in this sense, they prevent “orphan” rows). A foreign key is required for each relationship identified on the ERD, and the foreign key will appear on the “many” side, and reference a candidate key (usually the primary key) on the “one” side.

A foreign key is a set of attributes (columns in SQL), and must match the parent key in both number of elements and data types. The column names can differ, but should normally match. You can satisfy both of these by copying the parent column definitions and pasting them into the child table (but check carefully whether the foreign key attributes should have NOT NULL constraints applied). We recommend that you declare foreign keys in SQL using named table-level constraints, like so:

constraint Enrolment_Student_FK foreign key (Student_ID) references Student,

If the column names differ, identify the corresponding columns in the parent table like so:

references Student (Student_ID),

The foreign keys in your schema will dictate the order of your CREATE TABLE statements. Since a foreign key always references a candidate key from the parent table, that parent table must exist first. For example, in a student records system, you would have to create the Paper and Student tables before you could create the Enrolment table. If you imagine moving the crow’s foot signifying the “many” side along the relationship, it will resemble an arrow head pointing at the table that must exist first. You might even like to number the tables on your ERD as a guide to their order, for example:

{{/Labs/03/Images/Tables.svg}}

Similarly, when entering data into your tables, you must enter the parent rows first, e.g. you would have to record a paper and a student before you could enter an enrolment. DELETE and DROP TABLE statements would use the reverse order.

If a foreign key references a composite candidate key, the foreign key will also be composite.

Questions

Which of Scientist, Sample, and Site will have foreign keys?
What columns would make up each foreign key?

Which foreign keys would be mandatory (NOT NULL)?
In what order would the tables need to be created?
Modify your CREATE TABLE statements to implement these, and test that they are correctly enforced.