Re: column being created as varchar() when char() requested.

[Philip Walden]

Michael Stassen wrote:

Philip Walden wrote:

Michael Stassen wrote:

You need supp_cd to be first in an index.  Try

ALTER TABLE hpi_supp_agmt ADD INDEX supp_ind (supp_cd);

then try to add your foreign key constraint again.

Michael

Looks like that does not work. BTW this does work (without adding a 
separate index) in postgres.

mysql> alter table hpi_supp_agmt add index i1hpisuppagmt (supp_cd);

Query OK, 28635 rows affected (4.89 sec)

Records: 28635  Duplicates: 0  Warnings: 0


OK, now table hpi_supp_agmt has supp_cd first in the i1hpisuppagmt 
index, and table supp has supp_cd first in the p1supp primary key 
index, so supp_cd should now be a foreign key candidate.

mysql> alter table hpi_supp_agmt  add constraint f1hpisuppagmt 
foreign key (hpi_no) references hpi;

ERROR 1005 (HY000): Can't create table './gem/#sql-c3f_2.frm' (errno: 
150)


But here you try to add a foreign key constraint using hpi_no, which 
is not first in any index.  And what is hpi?  Did you try your 
original statement?

alter table hpi_supp_agmt add constraint foreign key (supp_cd)
references supp(supp_cd);


I would expect this to work now.  At least, it does for me.

Michael
You're right, I guess I was getting a bit punchy that late at night.

However, when I go back and try the correct  ALTER statement:

mysql> alter table hpi_supp_agmt add constraint foreign key (supp_cd) references 
supp(supp_cd);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
It still fails :-(

Could be that the primary index is first in the create table statement 
and it is hiding the index with just supp_cd in it?

mysql> show create table hpi_supp_agmt;



| hpi_supp_agmt | CREATE TABLE `hpi_supp_agmt` (

`div_cd` char(4) NOT NULL default '',

`hpi_no` char(15) NOT NULL default '',

`supp_cd` char(5) NOT NULL default '',

`agmt_no` char(8) NOT NULL default '',

`agmt_owner` char(4) NOT NULL default '',

`agmt_price` decimal(16,0) NOT NULL default '0',

`agmt_uom` char(4) NOT NULL default '',

`agmt_lt` int(11) NOT NULL default '0',

`agmt_exp_dt` date NOT NULL default '0000-00-00',

`updt_user_id` smallint(6) NOT NULL default '0',

`updt_dt` date NOT NULL default '0000-00-00',

`create_dt` date NOT NULL default '0000-00-00',

PRIMARY KEY  (`div_cd`,`hpi_no`,`supp_cd`,`agmt_no`),

KEY `i1hpisuppagmt` (`supp_cd`)

) TYPE=InnoDB DEFAULT CHARSET=latin1 |

Phil



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]