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]
