What is a composite foreign key in mysql?

Question

Seeing this term (composite foreign key) in the documentation for a framework I am using (yii). What is a composite foreign key (in a mySql database)?

(My guess is that, given a relationship between two tables, one table has a column with the exact same name as the id the other table.)

*Disclaimer: I did my due diligence and Googled this for like two whole minutes but found no conclusive definition for the term..

Answer 1

Suppose we have a table of users:

+---------+----------+------------+------------+----------------+
| Surname | Forename | ZIP        | DOB        | Email          |
+---------+----------+------------+------------+----------------+
| Jones   | John     | 60612-0344 | 1970-02-14 | [email protected] |
| Jones   | Jane     | 60612-0344 | 1971-05-26 | [email protected] |
| Smith   | Sara     | 19002-0052 | 1982-06-21 | [email protected] |
+---------+----------+------------+------------+----------------+

Because our application requires every user to have their own distinct email address, we can uniquely identify records within the table by the value in the Email column: it forms a key into the table. Such keys (defined over a single column) are said to be simple.

In some situations, one might know that no two users can have the same name, date of birth and ZIP code: then another possible key would be the combination of (Surname, Forename, ZIP, DOB). Such keys (defined over multiple columns) are said to be composite.

Since each record's key must (by definition) be unique to it, one can tell MySQL to enforce such uniqueness constraints by defining a UNIQUE index over the relevant columns (the table's PRIMARY KEY is a special type of UNIQUE index): attempts to create (or update) a record with the same key as an existing record will fail.

Now suppose one has a table of orders:

+--------------+-----------+---------+----------+
| Order_number | Status    | Total   | Customer |
+--------------+-----------+---------+----------+
|        12345 | Completed | 1234.99 |     ?    |
|        12346 | Pending   |  345.00 |     ?    |
|        12347 | Cancelled | 9876.50 |     ?    |
+--------------+-----------+---------+----------+

We wish to associate orders with the relevant record from the users table. But how to do that? What do we put in the Customer column?

Clearly we wish to identify a unique record in the users table, so we need to use one of its keys (such as Email in the first example above). Using one table's key to reference its records from another table in this manner is extremely commonplace in relational databases: in such situations, we refer to the referencing column as being a foreign key (since it holds keys into a foreign table).

Should we use a composite key for the reference, we would have a composite foreign key. Under the second example above, our orders table might have columns Customer_Surname, Customer_Forename, Customer_ZIP and Customer_DOB which would together form a foreign key into the users table (in this case, I wouldn't recommend such a schema).

MySQL can not only enforce foreign key constraints (ensuring that the referenced record exists in the foreign table), but can also automatically update or delete the referencing (orders) table if the referenced record is itself updated or deleted. For example, if John was deleted from the users table, all of his orders could be automatically purged from the orders table (again, probably not what one wants in this case); or if his email address changed, the Customer column could be automatically updated.