SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Referencing itself for a foreign key?

    Hey,

    I can't seem to add a foreign key and I'm not sure why. Here is the error message:

    1005 - Can't create table 'dummy.#sql-cc4_b8' (errno: 150)

    It happens when I try this:

    Code MySQL:
    ALTER TABLE t_comments
    ADD FOREIGN KEY (userid)
    REFERENCES users(id)

    The table users is MYISAM and the t_comments is innoDB, I'm not sure if thats why its causing an issue?

    thank you

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    both tables have to be innodb, i believe
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes that's the issue. Both should be InnoDB.

    If both are MyISAM you wouldn't get an error, but MyISAM doesn't enforce foreign keys so it wouldn't be useful either.

  4. #4
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have a contraint UNQIUE on a column called alias in my users table. Would I take a hit in performance when doing an INSERT? Seeing as it has to search al he rows for it. I hear innoDB isn't good for that

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    no, no "hit"

    and innodb is just fine at doing an index lookup -- you need to start listening to other sources
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont understand what you mean by "other resources" ?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    he said "I hear innoDB isn't good for that"

    so i said "you need to start listening to other sources"

    get it?

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

  8. #8
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    aaahh I get you now.

    Hehehe thank you.

  9. #9
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm having a small issue

    When I do an INSERT it fails and gives me an error message

    Code:
    Cannot add or update a child row: a foreign key constraint fails
    When I do the INSERT

    Code MySQL:
    INSERT INTO help (userid, title, message, date)
    VALUES (1, "The title", "The content!", NOW())

    userid column has a FOREIGN KEY that references the users table id column. Both are innoDB this time.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    apparently you do not have a row in the users table with that id value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's a dummy database hosted locally. It only has one row with the value of 1.... :/

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    whose thread is this? i feel like i'm trying to answer similar questions from two different guys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry same person. I was answering from my iPhone and my PC.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    well, i'm sorry to have to tell you this, but it is against sitepoint rules to have more than one account

    please decide which one you would like to keep, because a moderator will soon be contacting you to have one of your accounts banned

    meanwhile, could you dump the tables please -- this involves using mysqldump which generates the CREATE TABLE statements as well as the INSERT statements to populate the rows

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

  15. #15
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No biggy.

    Here is the information you asked for.

    Users table:
    Code MySQL:
    CREATE TABLE IF NOT EXISTS `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `alias` varchar(255) NOT NULL,
      `email` varchar(255) NOT NULL,
      `password` varchar(255) NOT NULL,
      `date` varchar(20) NOT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `alias` (`alias`,`email`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    Theres only one row in the users table, which is me.

    Code MySQL:
    INSERT INTO users (alias, email, password, date)
    VALUES ("ybh", "email@email.com", "password", NOW())


    help table:
    Code MySQL:
    CREATE TABLE IF NOT EXISTS `help` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `userid` int(11) NOT NULL,
      `title` varchar(255) NOT NULL,
      `help` varchar(4000) NOT NULL,
      `date` varchar(20) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `userid` (`userid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

    When I try to do an INSERT in help it gives me the error message.

    Code MySQL:
    INSERT INTO HELP (userid, title, message, DATE)
    VALUES (1, "The title", "The content!", NOW())

    thanks

    EDIT:

    Not sure if you need this:

    Code MySQL:
    ALTER TABLE `help`
    ADD FOREIGN KEY ( `userid` )
    REFERENCES `dummy`.`users` (`id`);

  16. #16
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dropped the tables and recreated them and it seems to have fixed the issue. I think the problem came from altering the table while already containing an index as MyISAM with the same name and then switching over to innoDB and creating a foreign key with the same name.

  17. #17
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    whether it was the cause I can't say but, I have found that foreign keys have to have a unqiue (index) name so following on from there, I make sure that PKs are caled 'id' and their respective FK is given the full name eg user_id.

    For foreign keys, if you give one a nmae in one tanle, that name has to be unique throughout the db.

    (it's the index name I mean, not the col name).

    hth

    bazz
    Last edited by IBazz; Jan 17, 2010 at 20:15. Reason: bracketed clarification.


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
  •