I am writing this tutorial to explain some of the basic concepts of referential integrity using foreign key constraints in mysql.
Referential integrity simply means that when a record in a table refers to a corresponding record in another table, that the child record will be acted on accordingly.
Another definition that you might find usefull is that of a foreign key. A foreign key is a field that matches the primary key in another table.
A simple example to illustrate this is the parent record with multiple child records. Lets say a parent record has three child records. This means that all three child records belong to the parent record and are associated to the parent record via some key, usually a foreign key. Without referential integrity, if the parent record were to get deleted, the child records would become orphans because they dont belong to any other parent.
Obviously, you could delete any child record with your application but this can get messy. Plus, developers sometimes forget the schema of a database and the relationship between tables and tend to forget to remove or update child code. This is where Foreign key constraints come in.
Foreign key constraints, in so many words, basically states that whatever action is defined on the parent record, must be taken on the child. To continue with our example, say that we now have foreign key constraints in our tables and we want to delete the parent record. Because we have a foreign key constraint that states, sudo code, that if the parent record gets deleted, then any child record shall be deleted as well. This will prevent having orphan records in the database, which over time can increase the number of records plus having records that aren’t associated with any other record in the database.
Now, for more concrete examples. Lets create a few tables:
-> (
-> ParentID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> Name VARCHAR(40) NOT NULL,
-> PRIMARY KEY (ParentID)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> CREATE TABLE Child
-> (
-> ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
-> ParentID SMALLINT UNSIGNED NOT NULL,
-> Description VARCHAR(40),
-> FOREIGN KEY (ParentID) REFERENCES Parent (ParentID)
-> ON DELETE CASCADE ON UPDATE CASCADE
-> );
Query OK, 0 rows affected (0.05 sec)
Obviously this is a very simple example but yet very powerful. The focus on the two tables you just created should be on the “Child” table. The rule you just added in the Child table is
“FOREIGN KEY (ParentID) REFERENCES Parent (ParentID) ON DELETE CASCADE ON UPDATE CASCADE”.
Let me explain what this doing. The first part basically says that the foreign key in the child table is “ParentID”. This is the primary field for the Parent table. So the child record is always associated with the Parent record based on the ParentID. The next part of the rule states that it “REFERENCES Parent” or the Parent table and the ParentID column. Finally, the last section states that if the Parent record is deleted, then the child record will be deleted and this should “CASCADE” down to any other child records associated with the Parent. The same concept applies to the “ON UPDATE CASCADE”. If the parent record is updated, then the child record should also be updated.
TIP: You should always let the database do the deletes and updates between associated records. This will keep referential integrity in your database and also will keep your database clean.
IF YOU NEED TO SEE FOREIGN KEY CONSTRAINTS on your database, execute the command SHOW CREATE TABLE tablename.
If you need to add a foreign key after you have create the table, using our example, you can do the following:
ALTER TABLE Child ADD CONSTRAINT FK_parent FOREIGN KEY (ParentID) REFERENCES Parent (ParentId) ON DELETE CASCADE ON UPDATE CASCADE;
If you need to drop the foreign key rule, use the following command:
ALTER TABLE Child DROP CONSTRAINT FK_parent;
If you have any questions at all on this topic, please don’t hesitate to contact me.
