Hi! Ok, I looked in the manual and found the following:
http://www.mysql.com/doc/S/i/Silent_column_changes.html " If any column in a table has a variable length, the entire row is variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This doesn't affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster. See section 7 MySQL Table Types. " So the bug is actually a 'feature'. Of course it would be better if the parser would not change declared types of columns. If a table handler wants to store CHAR columns like VARCHAR columns in some cases, it should make the decision at a lower level. It is not the parser's job to decide this. To avoid that this feature prevents creation of foreign key constraints, I will allow a VARCHAR column to reference a CHAR column, and vice versa, in 3.23.47. 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 >Date: Sat, 22 Dec 2001 19:45:24 >To: [EMAIL PROTECTED] >From: Heikki Tuuri <[EMAIL PROTECTED]> >Subject: Re: Problems in Foreign Key Syntax > >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
