What is a complex foreign key in mysql?

Question

Seeing this term (compound foreign key) in the documentation for the structure used (yii). What is a composite foreign key (in mySql database)?

(I assume that given the relationship between the two tables, one table has a column with the same name as the identifier of the other table.)

* Disclaimer: I performed my due diligence and did this for two whole minutes, but did not find a final definition of the term.

Answer 1

A composite key consists of more than one attribute to uniquely identify an entity . This differs from a composite key in that one or more of the attributes that make up the key are not simple keys at its discretion.

For example, you have a database with your CD collection. One of the objects is called tracks, which store the details of the tracks on the CD. It has a CD name composite key, track number.

The name of the CD in the track object is a simple key associated with the CD object, but the track number is not a simple key.

Answer 2

Suppose we have a user table:

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

Since our application requires each user to have their own separate email address, we can uniquely identify entries in the table by the value in the Email column: it generates a key in the table. Such keys (defined by one column) are called simple.

In some situations, one might know that none of the two users can have the same name, date of birth, and zip code: then the other possible key would be a combination (Surname, Forename, ZIP, DOB) . Such keys (defined in several columns) are called compound keys.

Since each record key must (by definition) be unique to it, it can be said that MySQL must apply such uniqueness restrictions by determining the UNIQUE index by the corresponding columns (the PRIMARY KEY table is a special type of UNIQUE index): attempts to create (or update) a record with with the same key as the existing record will fail.

Now suppose we have an order table:

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

We want to associate orders with the corresponding entry from the users table. But how to do that? What do we put in the Customer column?

Obviously, we want to identify a unique entry in the users table, so we need to use one of its keys (for example, Email in the first example above). Using one table key to reference its records from another table is thus extremely common in relational databases: in such situations, we refer to the reference column as a foreign key (since it contains the keys in the foreign table).

If we use a composite key for reference, we will have a composite foreign key. In the second example above, our order table may have columns Customer_Surname , Customer_Forename , Customer_ZIP and Customer_DOB , which together form a foreign key in the users table (in this case, I would not recommend such a scheme).

MySQL can not only enforce foreign key constraints (ensuring that the reference record exists in the external table), but can also automatically update or delete table links (orders) if the link itself is updated or deleted. For example, if John was deleted from the user table, all his orders can be automatically deleted from the order table (again, probably not what is needed in this case); or if his email address has changed, the Customer column can be automatically updated.