Foreign key

Databases · Foreign keys

File the orders

Pick an order, then pick the customer whose primary key equals the customer ID stored in that order.

6 of 6 still to sort.

You're the database now. Each order only stores a customer ID, so match that number to a customer's primary key.

Predict, then check

Order 9001 sits in the Orders table. The Customers table only has customer IDs 101, 102 and 103.

Order 9001 stores customer ID 999. No record in Customers has 999. What can the database say about who placed it?

What you need to know

• A relational database keeps data in more than one table, and links the tables through shared fields.
• Every table has a primary key: a field whose value uniquely identifies each record.
• A foreign key is a field in one table that holds values of another table's primary key.
• Have a goYour classmate insists: 'Customer ID in the Orders table is a primary key. It's got ID in the name!' What do you tell them?

  It's a foreign key: it holds values of the Customers table's primary key.

  What a field holds decides what it is, not its name. A foreign key holds another table's primary key values.

• To follow the link, find the record in the other table whose primary key value equals your foreign key value.
• A foreign key doesn't have to be unique in its own table. Many orders can carry the same customer ID.
• When many records refer to one record like that, it's called a one-to-many relationship.
• Have a goIn an Orders table, customer ID 2 appears in four records and customer ID 5 in one. How many different customers do these five orders belong to?

  Two customers.

  Repeated foreign key values still point at the same record, so five orders can refer to just two customers.

• A foreign key value should match a primary key value that exists in the linked table.
• If it refers to a record that doesn't exist, the data is left inconsistent.
• Store data about each item once, refer to it by key, and you reduce data redundancy.
• Have a goAsha has placed 40 orders. How many copies of her address are stored if every order repeats it? And if it lives only in a Customers table?

  40 copies, against 1.

  Linked tables store data about each item once and refer to it by key, which reduces data redundancy.

The big picture

A foreign key is a field in one table that holds values of another table's primary key. Matching the two values links a record to the one it refers to. Unlike a primary key, a foreign key can repeat, which gives a one-to-many relationship. Every foreign key value should match an existing primary key, and storing data once and referring to it by key reduces redundancy.

⚠ Watch out

Believing a foreign key must be unique, or that customer ID in Orders is Orders' own primary key. In its own table a foreign key value can repeat. It's the primary key that never does.

Memory hook

A foreign key is a borrowed ID: it belongs to another table's primary key, and it can be borrowed as often as you like.

Check yourself

A Pets table stores an owner ID in every pet record, taken from the Owners table. Two pets hold the same owner ID. Is that a problem? Say why in one sentence.