SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    May 2010
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MySQL workbench error

    Hi I'm trying to set a foreign key to link tables within MySQL workbench and cannot work out why I'm getting the folowing error.

    SQL Error 1005: Can't create table 'feedback.#sql-174_8' (errno: 150)

    The SQL I'm trying to run is

    ALTER TABLE `feedback`.`question_answers`

    ADD CONSTRAINT `relation`

    FOREIGN KEY (`relation` )

    REFERENCES `feedback`.`user` (`relation` )

    ON DELETE SET NULL

    ON UPDATE CASCADE

    , ADD INDEX `relation` (`relation` ASC) ;


    The DB is called 'feedback' and I have tables called 'user' and 'question_answers' Both of the relation fields are set as (VARCHAR45) and I cannot get this link to function.

    Any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    could you do a SHOW CREATE TABLE for both tables please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    May 2010
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    CREATE TABLE `user` (
    `iduser` int(11) NOT NULL AUTO_INCREMENT,
    `firstname` varchar(255) NOT NULL,
    `surname` varchar(255) NOT NULL,
    `relation` varchar(45) NOT NULL,
    PRIMARY KEY (`iduser`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    'CREATE TABLE `question_answers` (
    `idquestion_answers` int(11) NOT NULL AUTO_INCREMENT,
    `idquestion` int(11) NOT NULL,
    `relation` varchar(45) NOT NULL,
    `answer` varchar(255) NOT NULL,
    PRIMARY KEY (`idquestion_answers`),
    KEY `idquestion` (`idquestion`),
    CONSTRAINT `idquestion` FOREIGN KEY (`idquestion`) REFERENCES `question_text` (`idquestion`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8'

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,245
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    ALTER TABLE user ADD UNIQUE (relation)

    this allows relation to be the target of the REFERENCES in question_answers

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


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
  •