MySQL
A foreign key is determined by the underlying database model. Usually, a foreign key points to the "primary key" of the table that it references. In Access, for example, a lookup field holds the ID of the record you are referencing, while displaying whichever field you specify from the referenced table. In
MySQL and most other RDBM systems, you can query the data using a LEFT JOIN or RIGHT JOIN based using the form "a.relatedid = b.id", where "a" is an alias to the child record (the one with the lookup field), and "b" is the primary key on the parent record.
A query on a foreign key might look like this: "select * from
orders a left join
customers b on a.
customerid = b.
id". In this query, you would see the order plus the customer that placed the order, each order on it's own line. Customers may be duplicated, since they might have multiple orders in the database. Of course, this assumes that "customerid" is the field on the "orders" table that contains the foreign key relationship, and "id" is a primary key on the "customers" table.
The primary key must be unique to be useful. This is usually defined as an AUTO_INCREMENT field, but it may also be some other value guaranteed to be unique, such as a randomly assigned UUID or customer number.
