Create a Foreign Key Constraint
How do I create a foreign key contraint?
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.
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
- 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)
[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.
ALTER TABLE classregistration ADD CONSTRAINT StudentsClassRegistration FOREIGN KEY (student_id_fk) REFERENCES students(student_id) ON UPDATE RESTRICT ON DELETE RESTRICT GO