Foreign Keys

In JOIN, we stated that the flight_id field in the flights table was a primary key...

JOIN ON boarding_passes.flight_id = flights.flight_id;

...what's this mean exactly?

A foreign key (FK) links to the primary key of a different, related record in another table.

In relation to Social Security Numbers...

While every U.S. citizen has a unique Social Security Number, they do not have a unique home address. There could be other potential family members or roommates living at the same address. Therefore, a home address to a U.S. citizen is the equivalent of a foreign key to a record.

Although home addresses are unique among houses, they are not unique among citizens.

If some U.S. citizens had the same home address, it wouldn't be an issue. We'd still be able to identify them uniquely with their Social Security Number and identify what specific house they live in with their home address.

Every record does not need to have a foreign key. They only need one if they're related to another record in a different table.

How to identify in schema

You can typically identify foreign keys within a given table if they have a "FK" next to them.

In the case of the boarding_passes table, we can see it has a foreign key of flight_id.

That's because multiple boarding passes can correspond to the same flight! Even though the flight itself is unique... it's not unique on all boarding passes. Some boarding passes can be related to the same flight!

Activate AutoScroll

You haven't unlocked all of BUS 104 yet...

Unlock our 42 concept breakdowns & 10 practice problems with guided solution walkthroughs!