I am having trouble creating a table with MySQL. I have narrowed the problem down to the enrolled table. The error I get is: #1005 - Can't create table 'mcems.enrolled' (errno: 150) http://pastebin.com/9cUkgs8p
3
-
2post some of your sql snippets to help us help youAndreas Wong– Andreas Wong2012-01-16 22:26:00 +00:00Commented Jan 16, 2012 at 22:26
-
I have that pastbin link there for that, I thought it might be too much text to post here. I can paste it all in here if that would be better?user471849– user4718492012-01-16 22:29:15 +00:00Commented Jan 16, 2012 at 22:29
-
@Joe, it's always best to ensure that the questions and answers remain useful even if the rest of the internet disappears totally. Since we now know what the problem is (see a1ex07 answer), we'll just copy across enough tables to make it self-complete.paxdiablo– paxdiablo2012-01-16 22:32:34 +00:00Commented Jan 16, 2012 at 22:32
Add a comment
|
1 Answer
CRN field should be the same type in both tables, MCEMS.courses and MCEMS.enrolled; otherwise, you cannot use it in FOREIGN KEY. You have
CREATE TABLE IF NOT EXISTS `MCEMS`.`courses` (
`CRN` INT NOT NULL , ...)
and
CREATE TABLE IF NOT EXISTS `MCEMS`.`enrolled` (
`user_id` INT NOT NULL ,
`CRN` VARCHAR(45) , -- must be INT (also NOT NULL because it's part of PK)!!!
PRIMARY KEY (`user_id`, `CRN`) ,
INDEX `user_id` (`user_id` ASC) ,
INDEX `CRN` (`CRN` ASC) ,
FOREIGN KEY (`user_id`)
REFERENCES `MCEMS`.`users` (`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (`CRN`)
REFERENCES `MCEMS`.`courses` (`CRN`)
ON DELETE CASCADE
ON UPDATE CASCADE)
Also, absolutely no need for INDEX user_id (user_id ASC)
