error on table creation attempt.

[IBazz]

I am trying to create a table and I get the error as shown below.

create table if not exists actual_facilities
( facility_id int not null auto_increment
, business_id int not null
, room_id int not null
, facility_name varchar(64) not null
, CONSTRAINT facilities_fk
   FOREIGN KEY (facility_name)
      REFERENCES possible_facilities(facility_name)
, CONSTRAINT business_id_fk
   FOREIGN KEY (business_id)
      REFERENCES business_details(business_id)
, CONSTRAINT facilities_room_id_fk
   FOREIGN KEY (room_id)
      REFERENCES bookings_rooms_stock(room_id)
, primary key (facility_id)
, unique key (business_id,room_id,facility_name)
) engine=innodb default charset=latin1;

#1005 - Can't create table 
'./my_DB/actual_facilities.frm' 
(errno: 121)

With regard to the constraints, the table names and columns are correctly named to mathc the other tables/cols.

If you have time, please can you lend me your eyes. I am missing something but cannot see what.

bazz

[r937]

all the PKs being referenced by your FKs must already exist, and must match datatype exactly

we would ahve to see those table definitions too, but i'm sure you can find the anomaly now that you know what to look for

[IBazz]

yup. Thanks Rudy.

I had a table named incorrectly and missed it because I had already fixed it earlier. I must have then gone on to use the previously copy and (not yet) pasted version. stoopid of me.

bazz