mysql - Adding foreign key Error 150 -

mysql - Adding foreign key Error 150 -

ok know there many questions out there error 150.

but says, " if answers not address question, please inquire new question." asking new question.

i have 2 tables tablea , tableb, both engine innodb

my query alter table tablea add together foreign key (url) references tableb(url) gives error 1005 (hy000): can't create table mydatabasename.#sql-3134 e52(error 150)

so here's did.

alter tablea drop column url; alter tablea add together column url varchar(100) not null default ""; alter tableb drop column url; alter tableb add together column url varchar(100) not null default ""; alter tableb add together primary key (url,bname,pid); alter tablea engine = innodb; alter tableb engine = innodb;

to confirm checked show table status both gave same innodb engine

then tried 1 time again same query same error. here's situation far

both tables using same engine both tables have compatible column url same datatype varchar(100) not null default ""

show create table tablea gives;

create table `tablea` ( `url` varchar(100) not null default '', `tno` int(11) not null, `bname` varchar(40) not null, `pid` int(11) not null, `oprno` int(11) default null, `found` int(11) default null, `fix` int(11) default null, `fixstatus` varchar(40) default null, `fixdate` date default null, `releasestatus` varchar(40) default null, `releasedate` date default null, primary key (`url`,`tno`), constraint `tablea_ibfk_1` foreign key (`url`) references `tableb` (`url`) ) engine=innodb default charset=latin1

show create table tableb gives

create table `tableb` ( `url` varchar(100) not null default '', `bname` varchar(40) not null, `pid` int(11) not null, primary key (`url`,`bname`,`pid`) ) engine=innodb default charset=latin1

update able add together 1 foreign key url using mysql workbench same error coming other fields

`alter table tablea add together foreign key (bname) references tableb(bname)`<br> `alter table tablea add together foreign key (pid) references tableb(pid)`

to create fk belows:

alter table tablea add together foreign key (bname) references tableb(bname); alter table tablea add together foreign key (pid) references tableb(pid);

you should create index(bname), index(pid) on tableb follows:

mysql> show create table tableb\g *************************** 1. row *************************** table: tableb create table: create table `tableb` ( `url` varchar(100) not null default '', `bname` varchar(40) not null, `pid` int(11) not null, primary key (`url`,`bname`,`pid`), key `bname` (`bname`), key `pid` (`pid`) ) engine=innodb default charset=latin1 1 row in set (0.00 sec) mysql> alter table tablea add together foreign key (bname) references tableb(bname); query ok, 0 rows affected (0.00 sec) records: 0 duplicates: 0 warnings: 0 mysql> alter table tablea add together foreign key (pid) references tableb(pid); query ok, 0 rows affected (0.00 sec) records: 0 duplicates: 0 warnings: 0

mysql