Can someone explain MySQL foreign keys

Question

I know what they are my question is, how do you link them or are they automatically linked when you have identical names in different tables. Here is an example:

Say I have an [orders] table and a [customer] table. Each row in the [orders] table has a customer_id number which is associated with the customer_id in the [customer] table. So how do I get the customer information by referencing the order? What would be the sql query?

Answer 1

... how do you link them or are they automatically linked when you have identical names in different tables.

This is not automatic, you have to add a foreign key constraint on the customer_id column of the order table. This can be done at the table creation time or using an ALTER statement. Check the documentation for the details. As others pointed out, note that both tables need to be InnoDB tables (foreign key constraints are not supported by the MyISAM storage engine that will ignore them).

With or without a FK constraint, the query would be something like:

SELECT * 
FROM CUSTOMER C, ORDER O
WHERE C.ID = O.CUSTOMER_ID
AND O.ID = ...

A FK constraint would "just" guarantee that the CUSTOMER_ID column of the ORDER table cannot contain values that doesn't exist in the CUSTOMER table (except potentially NULL) and thus enforce referential integrity.

Answer 2

Foreign keys are not automatically linked by common names. One thing that frequently confuses people about MySQL foreign keys is that the MyISAM table engine (the default) doesn't support foreign keys at all. Rather than giving an error message when adding a foreign key to a MyISAM table, MySQL silently ignores the foreign key definition. Foreign keys are supported by the InnoDB table engine so you should make sure all tables you wish to add foreign keys to are of the InnoDB table type. To add a foreign key you do something like this:

alter table fk_table add foreign key (fk_column) references pk_table (pk_column);