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)
>>
>>
>>
>>
>
>
