Create a Foreign Key Constraint

Question

How do I create a foreign key contraint?

Premise

You want to establish a relationship between two tables at the DBMS level. The main advantage of doing this to prevent inadvertant physical deletes of parent table rows that are referenced by rows in other tables.
Please note that I have marked this as advanced as there is a lot one must understand about table relationships and design in order to put this into practical practice.

Answer

For this example we will use the Course Registration databases classregistration and students tables. I have selected these two as they provide an easy business case with which to explain this concept

The students table contains a row for each student. This table contains various demographic information about the student (name, contact information, etc).

The classregistration table represents a list of seats and students registered for each class. This table contains the student ID number as a foreign key pointing to the students table.

If we do not place a foreign key constraint between these two tables there is nothing to prevent someone from deleting a student from the students table. If this happens and the student is registered for classes in the classregistration these rows would become what is referred to as orphan records.

By placing a foreign key constraint between these two tables we can prevent this scenario above from ever happening. In other words, with a foreign key constraint applied if someone tries to delete student number 5 from the students table and there is a coresponding key in the classregistration table the DBMS will not allow the delete to occur.

Consider for a moment the safety mechanism that this inherently places on our course registration database. You can assume that if a student is registered for classes we have bills to mail them, assignments and correspondance to deliver. By placing a foreign key constraint on these tables we can guarantee that the scenario I just presented never happens.

Code to Create Foreign Key (FK) Constraint

Important Considerations

Both tables must be InnoDB tables
Cannot be TEMPORARY tables.
Index is required on the foreign key (for performance reasons)
Foreign key column must be first column in index (again, for performance reasons)

Syntax

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

It is possible to add additional SQL code to deal with what you want the DBMS to do between the two tables when a DELETE or UPDATE occurs. This subject is one that I could spend pages upon pages describing. It is out of scope here.

Examples

ALTER TABLE classregistration ADD 
      CONSTRAINT  StudentsClassRegistration
      FOREIGN KEY (student_id_fk)
      REFERENCES  students(student_id)
      ON UPDATE RESTRICT ON DELETE RESTRICT 

GO

If you find this site useful and are a book buyer/reader... You can support my site by clicking on one of the Links below to Abe books By doing this, I will receive a small commision on your purchase and making your purchase will not cost you any more money than if you went directly the site yourself! Abe Books sells books at a fraction of the original cost. I have purchased many books from them originally cost $40.00 for as little as $5 to $10 dollars including shipping costs Click on applicable icon to find books at Abe Books on ...
MySQL	MySQL & Php

Abe Books is one of my affiliates. I only use affiliates that I also purchase products from. I love Abe books. If it is printed they likely have a copy. I have also included a generic link to Abe Books below. Once on their site, you will find that they have a nice, easy to use search engine so to find what you are looking for.