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
