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;
source to share
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.
source to share