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
>
>
>
