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?
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:
The Foreign Keys in all the other tables link to the same Primary Key in QiSQL_Parents