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 :-(
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?
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
