Thank you Bruce and Paul, Your explanation help clear a lot of doubts. So If i create another two tables, let say INVOICE2 and LINEITEM2 with the same construct, when i specify the index for the foreign key and referenced key, MYSQL will create a separate index for these two tables even i only use "index(INVOCE2NO)" without specifying a new name for the index like "index indx2(INVOCE2NO)" ?
Thanks alot. --- Bruce Feist <[EMAIL PROTECTED]> wrote: > Titu Kim wrote: > > >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. > > > An index can contain multiple columns. The ones > relevent to the primary > or foreign key must be the first IN THAT PARTICULAR > INDEX. For > instance, let's say that INVOICE is indexed on > invoiceno and LINEITEM is > indexed on the combination of invoiceno (first) and > lineno (second). > invoiceno could be set up as a foreign key in > LINEITEM, corresponding > to the invoiceno column in INVOICE, because > invoiceno is the first > column in the index. > > The reason is that within an index you can do an > efficient lookup as > long as you know the leftmost part of the key. A > good analogy is the > fiction section of a library, where books are sorted > by author's surname > followed by given name; this corresponds to a > two-column index. If you > know the surname, you can still find the author > quickly. If you know > only the given name, you cannot. > > Bruce Feist > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > __________________________________ 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]
