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
