List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 3 2004 10:15pm
Subject:Re: column being created as varchar() when char() requested.
View as plain text  
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)
>>>
>>>
>>>
>>>
>>
>>
> 
> 

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