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

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

 Collapse All    Expand All
Custom Search

MySQL Information and Resources