Results 1 to 9 of 9
  1. #1
    Join Date
    May 2012
    Posts
    14

    Unanswered: Database Model Forward Engineer SQL Script Error 1005

    I've been creating a database model which supports users, connections (friends), support tickets, feedback, logs, roles (permissions), etc…

    I have setup the tables to use InnoDB. After executing my script (which I can't tell what the problem is) I get the following error:

    Code:
    ERROR: Error 1005: Can't create table 'pluginst_global.global_users_email' (errno: 121)
    Database Model Forward Engineer SQL Script

    Any help would be appreciated, thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can't use the same name for more than one constraint or index
    Code:
    CREATE  TABLE IF NOT EXISTS `pluginst_global`.`global_users_meta` (
    ...
      CONSTRAINT `global_users`
        FOREIGN KEY (`user_id` )
        REFERENCES `pluginst_global`.`global_users` (`user_id` )
    
    CREATE INDEX `global_users` ON `pluginst_global`.`global_users_meta` (`user_id` ASC) ;
    
    CREATE  TABLE IF NOT EXISTS `pluginst_global`.`global_users_email` (
    ...
      CONSTRAINT `global_users`
        FOREIGN KEY (`user_id` )
        REFERENCES `pluginst_global`.`global_users` (`user_id` )
    
    CREATE INDEX `global_users` ON `pluginst_global`.`global_users_email` (`user_id` ASC) ;
    
    et cetera
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    14
    Thank you! I understand why it would not work now!

  4. #4
    Join Date
    May 2012
    Posts
    14
    I fixed the names and now I get the following error:

    Code:
    ERROR: Error 1005: Can't create table 'pluginst_global.global_connections' (errno: 150)
    I updated my original link to reflect the new names.

    Any help would be appreciated, thank you.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem is here --
    Code:
    CONSTRAINT `global_connections__global_users_FK`    
    FOREIGN KEY (`user_id` , `connection_user_id` )    
    REFERENCES `pluginst_global`.`global_users` (`user_id` , `user_id` )
    you can't reference a two-column unique key that doesn't exist -- global_users does not have two columns called user_id

    i'm beginning to think the forward-engineering tool you're using is pants
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2012
    Posts
    14
    It's actually the MySQL Workbench tool, so I'm actually surprised myself honestly!

  7. #7
    Join Date
    May 2012
    Posts
    14
    Also how should I resolve this problem since both the user_id and connection_user_id columns do both references the user_id column from another table.

    It's to create a 'friends' list.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by EpicKris View Post
    Also how should I resolve this problem since both the user_id and connection_user_id columns do both references the user_id column from another table.
    those would be separate foreign keys

    actually, now that i think about it, the combined foreign key was probably your doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2012
    Posts
    14
    Quote Originally Posted by r937 View Post
    those would be separate foreign keys

    actually, now that i think about it, the combined foreign key was probably your doing
    Actually that was my doing. Haha. It's quite obvious thinking about it now. It would be simpler if it could've been done with the same FK. Thank you!

Tags for this Thread

Posting Permissions

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