Hi Philip,
> > 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;
What if you create an additional index on the supp_cd column only?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com
