List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 5 2004 5:29am
Subject:Re: column being created as varchar() when char() requested.
View as plain text  
Philip Walden wrote:

> Michael Stassen wrote:
> 
>> 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
>>
> Looks like that does not work. BTW this does work (without adding a 
> separate index) in postgres.
> 
> mysql> alter table hpi_supp_agmt add index i1hpisuppagmt (supp_cd);
> 
> Query OK, 28635 rows affected (4.89 sec)
> 
> Records: 28635  Duplicates: 0  Warnings: 0

OK, now table hpi_supp_agmt has supp_cd first in the i1hpisuppagmt 
index, and table supp has supp_cd first in the p1supp primary key index, 
so supp_cd should now be a foreign key candidate.

> mysql> alter table hpi_supp_agmt  add constraint f1hpisuppagmt foreign 
> key (hpi_no) references hpi;
> 
> ERROR 1005 (HY000): Can't create table './gem/#sql-c3f_2.frm' (errno: 150)

But here you try to add a foreign key constraint using hpi_no, which is 
not first in any index.  And what is hpi?  Did you try your original 
statement?

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

I would expect this to work now.  At least, it does for me.

Michael


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