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
Philip Walden wrote:
This is not so great when I have another table without any variable columns, that is trying to add a foreign key reference to a column that has "silently" been changed to varchar(). That is my problem. I can't add the foreign key reference as the referenced table column has been changed and key types don't match.
In the example below, the table hpi_supp_agmt's column supp_cd is the original char(5).
mysql> alter table hpi_supp_agmt add constraint foreign key (supp_cd) references supp(supp_cd);
ERROR 1005 (HY000): Can't create table './gem/#sql-72f_4.frm' (errno: 150)
At least one should be able to add the foreign key to a silently changed column.
Thanks
Phil
Michael Stassen wrote:
As soon as you have a variable length column, you have variable length rows. In this case, mysql converts your char columns to varchar to save space and time. See the manual for the details <http://www.mysql.com/doc/en/Silent_column_changes.html>.
Michael
Philip Walden wrote:
I have MySQL 4.1.1-1. Given the create statement:
create table supp ( supp_cd char(5) not null, supp_nm varchar(48) not null, supp_abbr char(4) not null, supp_stat varchar(32) not null, sz_tp_class_cd char(2) not null, ownrshp_class_cd char(2) not null, city_nm varchar(32) not null, geog_area_nm varchar(32) not null, cnty_cd char(2) not null, dunn_bradstreet_no char(10) not null, updt_user_id smallint not null, updt_dt date not null, create_dt date not null, constraint p1supp primary key (supp_cd) ) type = InnoDB;
A descibe shows that supp_cd, supp_abbr and dunn_bradstreet_no are being added as a varchar() instead of a char(). Any ideas as what is wrong? Looks like a parsing error.
mysql> describe supp; +--------------------+-------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+------------+-------+ | supp_cd | varchar(5) | | PRI | | | | supp_nm | varchar(48) | | | | | | supp_abbr | varchar(4) | | | | | | supp_stat | varchar(32) | | | | | | sz_tp_class_cd | char(2) | | | | | | ownrshp_class_cd | char(2) | | | | | | city_nm | varchar(32) | | | | | | geog_area_nm | varchar(32) | | | | | | cnty_cd | char(2) | | | | | | dunn_bradstreet_no | varchar(10) | | | | | | updt_user_id | smallint(6) | | | 0 | | | updt_dt | date | | | 0000-00-00 | | | create_dt | date | | | 0000-00-00 | | +--------------------+-------------+------+-----+------------+-------+ 13 rows in set (0.01 sec)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
