List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:February 6 2004 2:52am
Subject:Re: column being created as varchar() when char() requested.
View as plain text  
Philip Walden wrote:
> Michael Stassen wrote:
> 
>>
>> 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
> 
> You're right, I guess I was getting a bit punchy that late at night.
> 
> However, when I go back and try the correct  ALTER statement:
> 
> mysql> alter table hpi_supp_agmt add constraint foreign key (supp_cd) 
> references supp(supp_cd);
> ERROR 1216 (23000): Cannot add or update a child row: a foreign key 
> constraint fails
> 
> It still fails :-(

This error means what it says.  There is at least one row in 
hpi_supp_agmt with a value in supp_cd that does not exist in supp.  You 
should be able to find these problem rows with something like

SELECT h.div_cd, h.hpi_no, h.supp_cd, h.agmt_no FROM hpi_supp_agmt AS h
LEFT JOIN supp AS s ON (h.supp_cd = s.supp_cd)
WHERE s.supp_cd IS NULL;

> Could be that the primary index is first in the create table statement 
> and it is hiding the index with just supp_cd in it?

I don't think so.  If mysql didn't see the index somehow, you'd get the 
same error as before.

> mysql> show create table hpi_supp_agmt;
> 
> | hpi_supp_agmt | CREATE TABLE `hpi_supp_agmt` (
> 
> `div_cd` char(4) NOT NULL default '',
> 
> `hpi_no` char(15) NOT NULL default '',
> 
> `supp_cd` char(5) NOT NULL default '',
> 
> `agmt_no` char(8) NOT NULL default '',
> 
> `agmt_owner` char(4) NOT NULL default '',
> 
> `agmt_price` decimal(16,0) NOT NULL default '0',
> 
> `agmt_uom` char(4) NOT NULL default '',
> 
> `agmt_lt` int(11) NOT NULL default '0',
> 
> `agmt_exp_dt` date NOT NULL default '0000-00-00',
> 
> `updt_user_id` smallint(6) NOT NULL default '0',
> 
> `updt_dt` date NOT NULL default '0000-00-00',
> 
> `create_dt` date NOT NULL default '0000-00-00',
> 
> PRIMARY KEY  (`div_cd`,`hpi_no`,`supp_cd`,`agmt_no`),
> 
> KEY `i1hpisuppagmt` (`supp_cd`)
> 
> ) TYPE=InnoDB DEFAULT CHARSET=latin1 |
> 
> Phil
> 

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