Re: Foreign Key in Innodb and Index Usages
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]
Re: Foreign Key in Innodb and Index Usages
At 11:40 -0700 6/7/03, Titu Kim wrote: 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]
Re: Foreign Key in Innodb and Index Usages
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]
Re: Foreign key in INNODB
equipID is a PRIMARY KEY ;) But you don't have index in which ifName is on the first place - and you yave to have it. Well!!! ifname also a Primary key!! ysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) [skip] -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED]Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: Foreign key in INNODB
On Thursday 16 January 2003 00:12, vinita Vigine Murugiah wrote: Rafal Jank wrote: On Wed, 15 Jan 2003 11:02:44 +1100 vinita Vigine Murugiah [EMAIL PROTECTED] wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice There is no index on ifname in table equipment_interfaces. Well.. Then how come Foreign key equipID in table equipment_interfaces worked?? There is no INDEX on equipID in table equipment?? equipID is a PRIMARY KEY ;) mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) [skip] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign key in INNODB
vinita Vigine Murugiah wrote: Egor Egorov wrote: On Thursday 16 January 2003 00:12, vinita Vigine Murugiah wrote: Rafal Jank wrote: On Wed, 15 Jan 2003 11:02:44 +1100 vinita Vigine Murugiah [EMAIL PROTECTED] wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice There is no index on ifname in table equipment_interfaces. Well.. Then how come Foreign key equipID in table equipment_interfaces worked?? There is no INDEX on equipID in table equipment?? equipID is a PRIMARY KEY ;) Well!!! ifname also a Primary key!! ysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) [skip] -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign key in INNODB
On Wed, 15 Jan 2003 11:02:44 +1100 vinita Vigine Murugiah [EMAIL PROTECTED] wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice There is no index on ifname in table equipment_interfaces. mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) *** mysql CREATE TABLE equipment_interfaces_IPv4alias ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifIPv4alias CHAR(20) NOT NULL, - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - INDEX (ifName), - FOREIGN KEY (ifName) REFERENCES equipment_interfaces (ifName) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName, ifIPv4alias) - ) TYPE=INNODB; ERROR 1005: Can't create table './test/equipment_interfaces_IPv4alias.frm' (errno: 150) mysql *** ** [vinita@mungah] vinita [1:54] perror 150 Error code 150: Unknown error: 150 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED]Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Traugutta 115c, 80-237 Gdansk, tel/fax. (58)5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Foreign key in INNODB
On Wednesday 15 January 2003 02:02, vinita Vigine Murugiah wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice [skip] mysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) ifName in the `equipment_interfaces` should be indexed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign key in INNODB
Rafal Jank wrote: On Wed, 15 Jan 2003 11:02:44 +1100 vinita Vigine Murugiah [EMAIL PROTECTED] wrote: HI I'm not sure what is wrong with my CREATE TABLE syntax, Please advice There is no index on ifname in table equipment_interfaces. Well.. Then how come Foreign key equipID in table equipment_interfaces worked?? There is no INDEX on equipID in table equipment?? mysql CREATE TABLE equipment ( - equipID CHAR(20) NOT NULL, - assetNumber CHAR(20), - serialNumber CHAR(50), - description BLOB, - barcode CHAR(20), - room CHAR(20), - area CHAR(20), - owner CHAR(100), - createDate DATE, - PRIMARY KEY (equipID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE equipment_interfaces ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifType ENUM(10Mbs,100Mbs,1Gb,CDDI,FDDI), - ifNet CHAR(50), - ifIPv4addr CHAR(20), - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) *** mysql CREATE TABLE equipment_interfaces_IPv4alias ( - equipID CHAR(20) NOT NULL, - ifName CHAR(20) NOT NULL, - ifIPv4alias CHAR(20) NOT NULL, - INDEX (equipID), - FOREIGN KEY (equipID) REFERENCES equipment (equipID) ON DELETE CASCADE, - INDEX (ifName), - FOREIGN KEY (ifName) REFERENCES equipment_interfaces (ifName) ON DELETE CASCADE, - PRIMARY KEY (equipID, ifName, ifIPv4alias) - ) TYPE=INNODB; ERROR 1005: Can't create table './test/equipment_interfaces_IPv4alias.frm' (errno: 150) mysql *** ** [vinita@mungah] vinita [1:54] perror 150 Error code 150: Unknown error: 150 -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- warm regards Vinita Vigine Murugiah Email : [EMAIL PROTECTED] Ph : (03) 8344 1273 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
