SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    error on table creation attempt.

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

    Code mysql:
    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;

    Code:
    #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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •