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
