Foreign Keys w/ InnoDb

hp829

Can anyone give me a straightforward explaination behind the theory and application of foreign keys using mysql's innodb table type. The mysql manual has some explaination, but it's extremely hard to follow and understand. I particular would like to know about the different specifications such as 'on delete cascade...'. Here is the complete syntax of a foreign key in the manual:

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
REFERENCES table_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]

if someone could briefly explain the parts of this statement, that would be great.

--Thank You in advance

eoghain

I'll give it a shot. For these examples I'm going to use the following tables:

phone_types
-----------------
phone_type_id int not null auto_increment
name varchar(20)

phone_numbers
---------------------
phone_number_id int not null auto_increment
phone_type_id int foreign_key(phone_types)
user_id int foreign_key(users)
number varchar(20)

users
-------
user_id int not null auto_increment
name varchar(50)

Here I have 3 tables 1 of which has foreign keys to the other 2. Now lets say I have the following data in each table:

phone_types
-----------------
1   | Cell
2   | Home
3   | Work

users
-------
1   | Bob
2   | Jane

phone_numbers
---------------------
1   | 1   | 1   | 111-111-1111     # Bob's Cell phone
2   | 1   | 2   | 222-222-2222     # Jane's Cell phone
3   | 2   | 1   | 333-333-3333     # Bob's Home phone
4   | 2   | 2   | 444-444-4444     # Jane's Home phone
5   | 3   | 1   | 555-555-5555     # Bob'sWork phone
6   | 3   | 2   | 666-666-6666     # Jane's Work phone

Ok, here we have 3 numbers for each of our users of 3 different types. Now to show how the On Delete functionality works we will delete the Cell entry from the phone_types table.

Cascade: All records in the phone_numbers table that have a phone_type_id of 3 will be deleted.

Set Null: All records in the phone_numbers table that have a phone_type_id of 3 will have that field set to null

No Action: Nothing will happen to the phone_numbers table

Restrict: You will be told that you can't delete this record because there are child records. If you manually deleted all rows in the phone_numbers table with a phone_type_id of 3 then you would be allowed to complete your deletion of the Cell row in the phone_types table.

These actions are basically the same as what happens for the On Update.

Foreign keys are typically used to make sure your tables get valid data inserted into them. For example we shouldn't be allowed to put a new record in our phone_numbers table with a phone_type_id of 4, since we don't have a phone_type of 4 defined. This foreign key will prevent a user from entering invalid data that may screw up other parts of our system.

I hope this helps.

hp829

Thank you for that great explaination. I do have a few more questions in order to clear up all of my confusion.

1.)
Looking at the syntax to create a table, I see that you can reference to a table without necessarily saying that the column should be a foreign key.

Here are the 2 methods of referencing an external table using your telephone example:


create table phone_types (
phone_type_id int not null auto_increment,
...
);

---Method 1---
create table phone_numbers (
...
phone_type_id int not null references phone_types (phone_type_id)...,
...
);

---Method 2:---
create table phone_numbers (
...
phone_type_id int not null,
index (phone_type_id),
foreign key (phone_type_id) references phone_types(phone_type_id)...,
...
);

I suppose both of these are valid statements, so my question is what is the real advantage to using the foreign key syntax as opposed to the other. I am guessing the difference is that with a foreign key, the column must first be indexed.

2.)
what does [CONSTRAINT [symbol]]? I see that it is optional, but can be used in several places, including a foreign key definition.

3.)
do foreign keys have any affect on using the SELECT statement, specifically in using different types of table joins?

4.)
One last question, this has nothing to do with foreign keys. I have always wondered whether you could do a join between 2 tables that are in 2 separate databases. Is this possible?

-- Once Again, I appreciate all your help

eoghain

InnoDB only uses what you've listed as method 2. This is because InnoDB requires that your key columns be indexed and since it won't create those indexes on it's own, you have to define the foreign key constraint after you've created them manually. InnoDB MySQL Manual Page
Constraint is used for various things, including foreign keys. Like primary key, not null, enum and other types of structures. This is just another way of defining them.
No there is no effect on your selects when dealing with columns that are foreign keys.

I've never had need to do this, but I just tested it out using the mysql shell, and I was able to select data from 2 databases using this syntax:

select DatabaseName.TableName.FieldName, DatabaseName.TableName.FieldName
from DatabaseName.TableName, DatabaseName.TableName;

i.e.
select test.phone_types.name, test2.phone_types.name
from test.phone_types, test2.phone_types;

So I guess you can do it, but I don't know why you'd ever want to.