Learning SQL - Foreign Keys

Foreign Keys

Don't worry - you don't need to learn a foreign language or even take a trip abroad to use foreign keys.

A Foreign Key is used to link one table to another.

Remember our little Data Model diagram from our Joins lesson?

Data Mdoel 1

This shows the use of a foreign Key in action.

The columns in the two tables are:

QiSQL_Parents QiSQL_Children
id integer primary key id integer primary key
first_name text parent_id integer
last_name text first_name text
title text last_name text

So by using this query we can get a list of all children that belong to Susan Brown

select child.first_name
from QiSQL_Children child,
QiSQL_Parents parent
where child.parent_id = parent.id
and parent.first_name = 'Susan'
and parent.last_name = 'Brown';

The important part of this Query is the 'where child.parent_id = parent.id' - we are linking the Primary Key in the QiSQL_Parents (id ) table to the Foreign Key ( parent_id ) in the QiSQL_Children table.

This type of linking relationships are often called Master / Detail relationships or Parent / Child relationships.

Creating A Foreign Key

You will probably have noticed that we did not specifically tell the database that parent_id was a foreign key when we created the QiSQL_Children table. We just defined the column as being an integer.

You do not NEED to specifically define a column as being a Foreign Key when you create a table, BUT you will see later in this course why it is important and how to do this.

We also did not tell the database which Table our Primary Key in QiSQL_Parents linked to - this is because Primary Keys may link to Foreign Keys in many tables. For example our Parents may have many cars, many jobs etc. so our data model could look like this:

Data Model 2

The Foreign Keys in all the other tables link to the same Primary Key in QiSQL_Parents