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

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

First result when searching for "mysql foreign key" on google. Complete with examples.

Answer 2

Foreign Keys in mysql are only available to certain database engines. MyISAM not being one of them.

I've attempted to use them with InnoDB and while it did guarantee consistency of data, I found it to be a huge performance hit, causing many locked tables that should never have been locked.

The main advantage of using foreign keys is to make sure that you never have an order that doesn't have a customer in the database. But you'll still need to access the customer by query.

select * from customer, order where order.customer_id = customer.id and order.id = 5

The above query shows you a general idea of how to access it via a lazy join, although it's dry-coded and I may have a typo or two in there.

My general thoughts on foreign keys is that they're great, but not usable in MySQL and that Postgres handles them much better.

Answer 3

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);

Answer 4

... 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 5

I'll assume you're asking because you read the documentation and it didn't make sense to you.

A foreign key (FK) is a field in TableB that holds the same value as a field, usually the primary key (PK), in TableA. So in pseudocode:

Create TableA:
A_id is PK,
somefield,
anotherfield

Create TableB:
B_id is PK,
A_id is FK to TableA,
farmfield,
outstandingfield

Create constraint on TableB:
In TableB A_id references TableA(A_id),
Don't allow updates to TableA(A_id),
Delete records from TableB that have the same A_id as deleted records in TableA

Then when you do a query that involves both tables, you can join on your FK:

SELECT a.somefield, b.farmfield FROM TableA a JOIN TableB b ON (a.A_id=B.A_id);

And when you delete from TableA, you don't have to delete from TableB. It will happen automatically because the constraint said "Delete records from TableB that have the same A_id as deleted records in TableA".

And when you insert into TableB, you'll either have to give a valid A_id, or, depending on how you defined the column, leave A_id =NULL.