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