Amit, thank you for a bug report. There seems to be a bug in the MySQL parser, because MySQL internally makes the first column of the table AttributeValue as of type VARCHAR(25), though you have declared it as CHAR(25).
Since the internal type of the referenced column is CHAR(25) but the referring column is VARCHAR(25), InnoDB gives an error and does not accept the declaration. Workaround: declare Attribute as VARCHAR(25) in both tables and Scenario as VARCHAR(10) in both tables. I have forwarded this email to Monty so that he can fix the MySQL parser. I tested also with the following declarations, and in these cases the MySQL parser works ok: Attribute is internally a CHAR(25) column. CREATE TABLE AttributeValue6(Attribute CHAR(25) NOT NULL, INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES Attribute (Attribute) ) TYPE = INNODB; CREATE TABLE AttributeValue100(Attribute CHAR(25) NOT NULL, INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES Attribute(Attribute) , Scenario CHAR(10) NOT NULL, INDEX sce_ind(Scenario), FOREIGN KEY (Scenario) REFERENCES Scenario(Scenario) ) TYPE = INNODB; Maybe the bug is that the VARCHAR definition of the column Value is in the parser put also on other columns. Regards, Heikki Tuuri Innobase Oy --- Order commercial MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB >Hi!! >I am trying create the following tables:- >CREATE TABLE Scenario ( >Scenario CHAR(10) NOT NULL PRIMARY KEY, >CopyFromScenario CHAR(10)) TYPE = InnoDB; > >CREATE TABLE Attribute( >Attribute CHAR(25) NOT NULL PRIMARY KEY,Type CHAR(25)) TYPE = InnoDB; > >CREATE TABLE AttributeValue(Attribute CHAR(25) NOT NULL, >INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES >Attribute(Attribute) ,Value VARCHAR(150) NOT NULL, Scenario CHAR(10) NOT NULL, >INDEX sce_ind(Scenario), FOREIGN KEY (Scenario) REFERENCES Scenario(Scenario) >) TYPE = INNODB; > >But for the third table AttributeValue it is giving >the error as:- >ERROR 1005: Can't create table >'.\netaps\AttributeValue.frm' (errno: 150 >Please help >Urgent >Thanks >Amit Lonkar. >Amit CREATE TABLE Scenario (Scenario CHAR(10) NOT NULL PRIMARY KEY, CopyFromScenario CHAR(10)) TYPE = InnoDB; CREATE TABLE Attribute( Attribute CHAR(25) NOT NULL PRIMARY KEY, Type CHAR(25)) TYPE = InnoDB; CREATE TABLE AttributeValue(Attribute CHAR(25) NOT NULL, INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES Attribute(Attribute) , Value VARCHAR(150) NOT NULL, Scenario CHAR(10) NOT NULL, INDEX sce_ind(Scenario), FOREIGN KEY (Scenario) REFERENCES Scenario(Scenario) ) TYPE = INNODB; CREATE TABLE AttributeValue6(Attribute CHAR(25) NOT NULL, INDEX par_ind(Attribute), FOREIGN KEY (Attribute) REFERENCES Attribute (Attribute) ) TYPE = INNODB; --------------------------------------------------------------------- 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
