CODEWITHSUNDEEP
mysqlsqlforeign-keys
Michael Ryan Soileau
Michael Ryan Soileau

Reputation: 1833

MySQL failing to create a new table using Foreign Keys

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;

Upvotes: 0

Views: 58

Answers (1)

maxtwoknight
maxtwoknight

Reputation: 5346

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.

Upvotes: 1

Related Questions