MySQL failing to create a new table using Foreign Keys

Question

I only get an error 150 when I run this. The other tables are InnoDB, the types are primary key, and the data types match. So I can't see what I'm doing wrong, (and I see no obvious syntax errors). Any ideas?

CREATE TABLE grouptosite (
  groups_id BIGINT(20),
  usertogroup_groupID BIGINT(20),
usertogroup_userID BIGINT(20),
  usertosite_id INT(10),

  gts_id INT(10) AUTO_INCREMENT NOT NULL,
  PRIMARY KEY (gts_id),
index (gts_id),
index (groups_id),
  index (usertogroup_groupID), 
index (usertogroup_userID),
  index (usertosite_id), 

FOREIGN KEY (groups_id)
    REFERENCES groups(id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (usertogroup_groupID, usertogroup_userID)
    REFERENCES usertogroup(groupID, userID)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (usertosite_id)
    REFERENCES usertosite(id)
    ON UPDATE CASCADE ON DELETE RESTRICT  
) ENGINE=INNODB;

This should solver your issues http://stackoverflow.com/questions/825362/mysql-error-150-foreign-keys — user2600629, Dec 17 '13 at 16:54
What do the `groups`, `usertogroup` and `usertosite` tables look like? — Explosion Pills, Dec 17 '13 at 17:17
Are you sure your columns are the same type? I first created the usertosite table with the id of BIGINT (following the same pattern of the other tables), but the grouptosite.usertosite_id field was INT. http://sqlfiddle.com/#!2/d821a — xecaps12, Dec 17 '13 at 18:01
Xecaps12 is correct. I can't give you the checkmark, but that solved it. — Michael Ryan Soileau, Dec 17 '13 at 20:40
@xecaps12: Please consider reposting your suggestion as an answer. I believe having this question answered (*both* actually and formally) would be a valuable addition to the site. Even though this case appears to have been perfectly covered by the docs, adding prominence to the `int`/`bigint` confusion may be helpful to the general public. — Andriy M, Dec 24 '13 at 06:55
@AndriyM It seemed like a simple typo and user2600629 had the right idea, but your right, clarity is good and I posted it as an answer. — xecaps12, Dec 24 '13 at 19:34

score 1 · Answer 1 · edited Dec 24 '13 at 22:07

1

Are you sure your columns are the same type? I first created the usertosite table with the id of BIGINT (following the same pattern of the other tables), but the grouptosite.usertosite_id column was INT: http://sqlfiddle.com/#!2/d821a.

As mentioned in another comment, datatypes for foreign keys need to be the same.

edited Dec 24 '13 at 22:07

Andriy M

76,112
17
94
154

answered Dec 24 '13 at 19:30

xecaps12

5,316
3
27
42

MySQL failing to create a new table using Foreign Keys

1 Answers1