Hmmm..., it doesn't seem to be a problem for me in 4.0.17:
mysql> CREATE TABLE t3
-> (
-> id CHAR(5) NOT NULL,
-> description VARCHAR(48) NOT NULL,
-> PRIMARY KEY (id)
-> ) TYPE = InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> DESC t3;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id | varchar(5) | | PRI | | |
| description | varchar(48) | | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> CREATE TABLE t4
-> (
-> id INT NOT NULL,
-> t3_id CHAR(5) NOT NULL,
-> INDEX t3_ind(t3_id)
-> ) TYPE = InnoDB;
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE t4 ADD CONSTRAINT FOREIGN KEY (t3_id) REFERENCES
t3(id);
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
I do get the error you quote (ERROR 1005: Can't create table...) if I
leave out the index creation in either table, which is documented in the
manual
<http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html>. I
can see that supp.supp_cd is a PRIMARY KEY. Did you create the required
index on hpi_supp_agmt.supp_cd before you tried to add the foreign key
reference?
Michael
Philip Walden wrote:
> 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)
>>>
>>>
>>>
>>>
>>
>>
>
>
