Please look and help. Error Code: 1005 cannot create table (erno150)

[kc vidal]

This stems back to a problem i had last friday that did not get any help on. I added the correction to the problem and inserted it below for the correction. But, I am stuck again. Some help with this would be AWESOME!! :D

I tried this to add a foreign key designation to my city_id field in my city_ref table using the following code:

alter table city_ref 
	ADD FOREIGN KEY city (city_id)
			REFERENCES city (city_id)
			ON DELETE SET NULL
			ON UPDATE CASCADE;

but I got a weird error:

Error Code: 1005
Can't create table 'new_pme.#sql-8f4_e' (errno: 150)


here is my table structure:

create table pme.refcity(
     annum integer(10),
     reference varchar(1000),
     info varchar(1000),
     city varchar(100));
load data infile 'my file extension here'
  into table refcity
     fields terminated by ','
     enclosed by '"'
     lines terminated by /r/n'
     ignore 1 lines;
select trim(both '/r/n' from city)
from refcity;


create table pme.city(
     city varchar(100),
     country varchar(100),
     province varchar(50),
     flag varchar(15),
     info text);
load data infile 'my csv file extension here'
  into table pme.city
     fields terminated by ','
     enclosed by '"'
     lines terminated by /r/n'
     ignore 1 lines;
select trim (both '/r/n' from info)
from pme.city;

create table new_pme.city(
     city_id mediumint not null auto_increment comment 'xxx')
       primary key (city_id),
     city varchar(100) not null comment 'xxx',
     city_info varchar(2000) comment 'xxx'
     )engine=innodb;
insert into new_pme.city(
     city,
     city_info
     )
select city,
     info
from pme.city;


create table new_pme.city_ref
    ENGINE = INNODB
    COMMENT = 'contains reference, cityref_id, details, year, name'
  select p1.city, 
        p1.city_id, 
	p2.reference,
	p2.annum,
	p2.info
    from city as p1, pme.refcity as p2
    where p1.city = p2.city;

ALTER TABLE city_ref
  ADD cityref_id  MEDIUMINT NOT NULL AUTO_INCREMENT 
  COMMENT 'UNIQUE ID FOR THE city REFERENCE MATERIAL' 
  primary key;

some files you could save as a csv to test it.

in old database = pme
city.csv

city,country,province,flag,info
vancouver,canada,bc,n,xxxxxxx
burnaby,canada,bc,n,xxxxxxxxx
scarborough,canada,ont,n,xxxxx
washington,USA,Wa,n,xxxxxx
las vegas,USA,Nv,n,xxxxxx
edmonton,canada,ab,n,xxxxxx
winnepeg,canada,sk,n,xxxxx

refcity.csv

annum,reference,info,city
1989,xxx,zzz,vancouver
1999,ssdfads,asdfdsf,burnaby
2009,dfsdf,fterre,las vegas


so if anyone can now help with the weird error in adding the foreign key back into the table i would be most appreciative