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. 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? Sorry for lengthy text. Regards, __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]