Search code examples
mysqlsqlforeign-keysmysql-error-150

Can't link two tables?

I'm sure this is something ridiculously simple, but I can't get my head around it.

Every time I try running this script, I get error number 150. I know that this is a foreign key issue. My other tables are fine and link to the projectregister table with no problems, but for some reason nothing wants to link to the userchar table.

I'm running this on a college server, so I cant try show engine innoDB status. Any ideas what's wrong here? Thanks

CREATE TABLE `userchar` (
  `userid` int(5) NOT NULL,
  `charname` varchar(25) NOT NULL,
  `charstats` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`userid`,`charname`),
  CONSTRAINT `userchar_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `projectregister` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `notes` (
  `userid` int(5) NOT NULL DEFAULT '0',
  `charname` varchar(25) NOT NULL,
  `usernote` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`userid`,`charname`,`usernote`),
  CONSTRAINT `notes_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `projectregister` (`userid`),
foreign key (charname) references userchar(charname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Solution

  • A foreign key must reference a unique value - be it a primary key or a plain old unique index.

    Here, you are attempting to make notes.userid reference projectregister.userid. However, projectregister.userid is not a unique value - only the combination of projectregister.userid and projectregister.charname is unique.

    You should either change the primary key or the foreign key definitions so that their column lists match.