List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 4 2004 5:29am
Subject:Re: column being created as varchar() when char() requested.
View as plain text  
You need supp_cd to be first in an index.  Try

ALTER TABLE hpi_supp_agmt ADD INDEX supp_ind (supp_cd);

then try to add your foreign key constraint again.

Michael

Philip Walden wrote:

> Michael Stassen wrote:
> 
>> 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
> 
> 
> The hpi_supp_agmt.supp_cd is part of a compound primary index. BTW, this 
> is a legacy database I am trying to port. Here is the create for the 
> hpi_supp_agmt:
> 
> create table hpi_supp_agmt
> (
>   div_cd char(4) not null,
>   hpi_no char(15) not null,
>   supp_cd char(5) not null,
>   agmt_no char(8) not null,
>   agmt_owner char(4) not null,
>   agmt_price decimal(16) not null,
>   agmt_uom char(4) not null,
>   agmt_lt integer not null,
>   agmt_exp_dt date not null,
>   updt_user_id smallint not null,
>   updt_dt date not null,
>   create_dt date not null,
>   constraint p1hpisuppagmt primary key (div_cd,hpi_no,supp_cd,agmt_no)
> ) type = InnoDB;
> 
> 
> Thanks
> Phil
> 
> 
> 

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