Foreign Keys

Foreign Keys

In this lesson, we’ll discuss foreign keys. Not all storage engines support foreign keys. In MySQL, InnoDB supports foreign keys but MyISAM doesn’t. Let’s try to understand the concept through the example we have been working on in this course. The DigitalAssets table consists of online accounts for actors in our Actors table. The two tables are related to each other by the columns ID and ActorID. Logically, it doesn’t make sense to have a row in the DigitalAssets table for an actor who is not listed in our Actors table. Ideally, we would want that anytime we add a new row to the DigitalAssets table a corresponding entry in the Actors table exists with the same ID as the ActorID of the new row being added to the DigitalAssets table. Similarly, if we delete any actor from the Actors table we’d want all rows in the DigitalAssets table associated with the actor to be deleted too. We can enforce these restrictions using the foreign key constraint.

The relation between the two tables is one to many. A foreign key can be a column or a group of columns in a table that link to a column or a group of columns in another table. In this case, the Actors table is the referenced table and called the parent table, whereas, the referencing table DigitalAssets is called the child table.

Example Syntax

CREATE TABLE childTable (

col1 <dataType>,

col2 <dataType>,

CONSTRAINT fkConstraint

FOREIGN KEY (col2)

REFERENCES parentTable(referencedCol);

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/41lesson.sh and wait for the MySQL prompt to start-up.