Can't link two tables?

I'm sure it's something ridiculously simple, but I can't get around it.

Every time I try to run this script I get error number 150. I know this is a foreign key problem. My other tables are fine and the projectregister table references are no problem, but for some reason nothing wants to refer to the userchar table.

I am running this on a college server so I cannot try to show the status of the innoDB engine. Any ideas what is wrong here? Thanks to

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;

      

+3


source to share


1 answer


The foreign key must refer to a unique value - be it a primary key or a simple old unique index.

Here you are trying to link notes.userid

projectregister.userid

. However, projectregister.userid

is not a unique value - a unique combination projectregister.userid

and projectregister.charname

unique.



You must either change the primary key or foreign key definitions to match their column lists.

+2


source







All Articles