Using indices will slow down inserts/updates on columns, and will use some amount of storage space (dependant on the size of the columns indexed, the number of columns indexed, etc.). However, they significantly improve performance when doing lookups in a table, which is especially important when executing joins (as you will most likely be doing between two tables tied by a foreign key). Thus, even if InnoDB did not require you to have an index on your FK columns (which it absolutely does), it almost certainly makes sense to have indices there anyway.
-Chris On Tue, 2002-09-17 at 02:03, kayamboo wrote: > Thanks a lot Chris > It worked for me. > > But I refered somewhere , that using excessive index will eat up resources. > So is it mandatory to use index even if I am not using them for something > like search? > > regards > Kayamboo Suresh > ----- Original Message ----- > From: "Chris Tucker" <[EMAIL PROTECTED]> > To: "kayamboo" <[EMAIL PROTECTED]> > Sent: Tuesday, September 17, 2002 5:35 PM > Subject: Re: table creation error with innodb/ sql,query > > > > InnoDB requires that you have an index on both ends of a foreign key > > constraint. Thus, you need to index the "type" field of the person > > table. Change your create statement to: > > CREATE TABLE person( > > name VARCHAR(50) NOT NULL PRIMARY KEY, > > type VARCHAR(50) NOT NULL, > > dob DATE, > > INDEX (type), > > FOREIGN KEY(type) REFERENCES relation(type) ON DELETE CASCADE > > ) TYPE = INNODB ; > > > > And you should have no problems (note that if "type" was not the primary > > key on the "relation" table you would also have to explicitly declare an > > index for that column). > > > > Chris > > > > On Mon, 2002-09-16 at 22:45, kayamboo wrote: > > > Hello experts > > > I am trying to create innodb tables with foreign key relations but I get > > > error 150 meaning that foregin key constraint can't be established. > > > > > > Here are the statements > > > > > > 1.CREATE TABLE relation( type VARCHAR(50) NOT NULL PRIMARY KEY)TYPE = > > > INNODB; > > > > > > 2.CREATE TABLE person( name VARCHAR(50) NOT NULL PRIMARY KEY, type > > > VARCHAR(50) NOT NULL, dob DATE, > > > FOREIGN KEY(type) REFERENCES > > > relation(type) ON DELETE CASCADE ) TYPE = INNODB ; > > > > > > But if I remove the type = innodb in the second statement, it is > executed. > > > Then I am using the ALTER TABLE syntax to create innodb tables. > > > > > > Any advice to avoid this alter table syntax? Or my create statement is > > > wrong? > > > > > > Thanks in advance > > > > > > Regards > > > Kayamboo Suresh > > > > > > sql,query,mysql > > > > > > > > > --------------------------------------------------------------------- > > > 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 > > > > > > > > > > > > > > > > --------------------------------------------------------------------- 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
