Forum Moderators: open

Message Too Old, No Replies

foreign key conflict

         

sfast

4:19 pm on Mar 15, 2007 (gmt 0)

10+ Year Member



desc locations;
+--------------+------------------+------+-----+---------+-------+
¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦
+--------------+------------------+------+-----+---------+-------+
¦ loc_name ¦ varchar(25) ¦ NO ¦ ¦ ¦ ¦
¦ zip_code_loc ¦ int(10) unsigned ¦ NO ¦ PRI ¦ 0 ¦ ¦
¦ address ¦ varchar(100) ¦ NO ¦ PRI ¦ ¦ ¦
¦ city ¦ varchar(25) ¦ NO ¦ ¦ ¦ ¦
¦ state_prefix ¦ char(2) ¦ NO ¦ ¦ ¦ ¦
¦ landmark ¦ mediumtext ¦ YES ¦ ¦ NULL ¦ ¦
+--------------+------------------+------+-----+---------+-------+
********************************************************************

Desc officeHrs;
+-----------+--------------+------+-----+---------+-------+
¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦
+-----------+--------------+------+-----+---------+-------+
¦ zip_code ¦ int(10) ¦ NO ¦ ¦ 0 ¦ ¦
¦ address ¦ varchar(100) ¦ NO ¦ ¦ ¦ ¦
¦ mon_on ¦ decimal(2,2) ¦ YES ¦ ¦ NULL ¦ ¦
¦ mon_off ¦ decimal(2,2) ¦ YES ¦ ¦ NULL ¦ ¦
¦ tues_on ¦ decimal(2,2) ¦ YES ¦ ¦ NULL ¦ ¦

*********************************************************************

Both tables were created with Engine = InnoDB option.

Alter table officeHrs Add Foreign Key (zip_code,address) REFERENCES locations(zip_code_loc,address);

The error I get -
ERROR 1005 (HY000): Can't create table '.\employ\#sql-838_8.frm' (errno: 150)

There is no table with sql-838_8.
Please help me. I am not able to figure out why the foreign key is not added.

sfast

4:22 pm on Mar 15, 2007 (gmt 0)

10+ Year Member



Sorry.

I was able to figure out. zip_code_loc was "unsigned".
And I did not have it in officeHrs for zip_code.

Thanks