List:General Discussion« Previous MessageNext Message »
From:Philip Walden Date:February 3 2004 7:53pm
Subject:Re: column being created as varchar() when char() requested.
View as plain text  
This is not so great when I have another table without any variable 
columns, that is trying to add a foreign key reference to a column that 
has "silently" been changed to varchar(). That is my problem. I can't 
add the foreign key reference as the referenced table column has been 
changed and key types don't match.

In the example below, the table hpi_supp_agmt's column supp_cd is the 
original char(5).

mysql> alter table hpi_supp_agmt add constraint foreign key (supp_cd) references
> supp(supp_cd);

ERROR 1005 (HY000): Can't create table './gem/#sql-72f_4.frm' (errno: 150)


At least one should be able to add the foreign key to a silently changed 
column.

Thanks

Phil

Michael Stassen wrote:

> As soon as you have a variable length column, you have variable length 
> rows.  In this case, mysql converts your char columns to varchar to 
> save space and time.  See the manual for the details 
> <http://www.mysql.com/doc/en/Silent_column_changes.html>.
>
> Michael
>
> Philip Walden wrote:
>
>> I have MySQL 4.1.1-1. Given the create statement:
>>
>> create table supp
>>  (
>>    supp_cd char(5) not null,
>>    supp_nm varchar(48) not null,
>>    supp_abbr char(4) not null,
>>    supp_stat varchar(32) not null,
>>    sz_tp_class_cd char(2) not null,
>>    ownrshp_class_cd char(2) not null,
>>    city_nm varchar(32) not null,
>>    geog_area_nm varchar(32) not null,
>>    cnty_cd char(2) not null,
>>    dunn_bradstreet_no char(10) not null,
>>    updt_user_id smallint not null,
>>    updt_dt date not null,
>>    create_dt date not null,
>>    constraint p1supp primary key (supp_cd)
>>  ) type = InnoDB;
>>
>> A descibe shows that supp_cd, supp_abbr and dunn_bradstreet_no are being
>> added as a varchar() instead of a char(). Any ideas as what is wrong?
>> Looks like a parsing error.
>>
>> mysql> describe supp;
>> +--------------------+-------------+------+-----+------------+-------+
>> | Field              | Type        | Null | Key | Default    | Extra |
>> +--------------------+-------------+------+-----+------------+-------+
>> | supp_cd            | varchar(5)  |      | PRI |            |       |
>> | supp_nm            | varchar(48) |      |     |            |       |
>> | supp_abbr          | varchar(4)  |      |     |            |       |
>> | supp_stat          | varchar(32) |      |     |            |       |
>> | sz_tp_class_cd     | char(2)     |      |     |            |       |
>> | ownrshp_class_cd   | char(2)     |      |     |            |       |
>> | city_nm            | varchar(32) |      |     |            |       |
>> | geog_area_nm       | varchar(32) |      |     |            |       |
>> | cnty_cd            | char(2)     |      |     |            |       |
>> | dunn_bradstreet_no | varchar(10) |      |     |            |       |
>> | updt_user_id       | smallint(6) |      |     | 0          |       |
>> | updt_dt            | date        |      |     | 0000-00-00 |       |
>> | create_dt          | date        |      |     | 0000-00-00 |       |
>> +--------------------+-------------+------+-----+------------+-------+
>> 13 rows in set (0.01 sec)
>>
>>
>>
>>
>
>

Thread
column being created as varchar() when char() requested.Philip Walden3 Feb
  • Re: column being created as varchar() when char() requested.Michael Stassen3 Feb
    • Re: column being created as varchar() when char() requested.Philip Walden3 Feb
      • Re: column being created as varchar() when char() requested.Michael Stassen3 Feb
        • Re: column being created as varchar() when char() requested.Philip Walden4 Feb
          • Monthly reportingScott Haneda4 Feb
            • Bash script to MySqlScott Haneda4 Feb
              • Re: Bash script to MySqlHassan Schroeder4 Feb
                • Re: Bash script to MySqlBernd Tannenbaum4 Feb
                  • RE: Bash script to MySqlRussell Horn4 Feb
                  • Re: Bash script to MySqlScott Haneda5 Feb
                    • Re: Bash script to MySqlBernd Tannenbaum5 Feb
                    • Re: Bash script to MySqlMichael Stassen5 Feb
                      • Re: Bash script to MySqlScott Haneda6 Feb
                        • Re: Bash script to MySqlMichael Stassen6 Feb
              • Re: Bash script to MySqlDuncan Hill4 Feb
              • User Defined FunctionBácskai Gergő" 4 Feb
          • Re: column being created as varchar() when char() requested.Michael Stassen4 Feb
            • Re: column being created as varchar() when char() requested.Philip Walden5 Feb
              • Re: column being created as varchar() when char() requested.Michael Stassen5 Feb
                • Re: column being created as varchar() when char() requested.Philip Walden6 Feb
                  • Re: column being created as varchar() when char() requested.Michael Stassen6 Feb
  • Re: column being created as varchar() when char() requested.Martijn Tonies4 Feb
RE: Bash script to MySqlDan Muey4 Feb