Hi, I have search the mailing list about the explanation for foreign key in Innodb. I also search goole groups. I have the following doubts.
1. From the documentation i read about the following. =============================================== Both tables have to be InnoDB type and there must be an index where the foreign key and the referenced key are listed as the FIRST columns. =============================================== I understand that both tables have to be innodb part. When the doc specifies both foreign key and referenced key must be listed as the FIRST columns in the index. I am confused.
You can create an index that includes multiple columns. For example, if you have first_name and last_name columns, you could create an index on both of them:
INDEX (last_name, first_name)
What the above means is that a foreign key/referenced key could be last_name (the first column in the index), but not first_name (the second column in the index).
(If you had a separate index on first_name, then you could use it for a foreign key/referenced key.)
For example ********************************** ==>create table parents(id int, name char(5), index(id)) Type=innodb;
==>create table child(id int, name char(5), parent_id int, index(parent_id), foreign key(parent_id) references parents(id)) Type=innodb; ************************************* Here parents.id is indexed and child.parent_id is also indexed. Can someone help to explain how these two indexes(parents.id and child.parent_id) are the "FIRST" in the index. To what the "FIRST" is relative to?
2. Does it mean if i create a third and fourth tables as follow
****************************** ==>create third_table (id int, name char(5), index(id)) TYpe=innodb;
==>create fourth_table (id int, name char(5), source_id int, index(source_id), foreign key (source_id) references third_table(id)) Type=innodb; ****************************** The id from 'third_table' is not the "FIRST" column anymore and thus the refererence key in "fourth_table" will not work?
In order to overcome the "FIRST" notion, does it mean i have to assign different index names to parents.id and third_table.id so both these two keys from different table will always be in the "FIRST" column in its index space.
3. Bottom line, i have to create different index name for all foreign keys like this?
========================================== create table parents (id int, name char(5), index first_ind(id)) Type=innodb; create table child (id int, parent_id int, name char(5), index first_ind(parent_id), foreign key (parent_id) references parents(id)) Type=innodb;
create third_table (id int, name char(5), index second_ind(id)) Type=innodb; create fourth_table (id int ,source_id int, name char(5), index second_ind(source_id), foreign key (source_id) references third_table(id)) Type=innodb; =============================================
4. Also what happen if i specified all the ids in parents, child, third_table and fourth_table as not null and primary key? Are they indexed automatically?
A PRIMARY KEY is an index, so if you create a PRIMARY KEY on each of those columns, they are indexed.
Sorry for lengthy text.
Regards,
-- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
