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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
