Altering table Foreign Keys produces an error (errno:150) (MySQL) This is the first database I set up so I apologize if my problem is something obvious.



I'm playing around with my table design and tried to create some link tables with FKs to avoid redundant data. When I try to sync my EER model with the DB I get an error which makes no sense to me.

Executing SQL script in server
ERROR: Error 1005: Can't create table 'jonijz1_Test.#sql-d7d_1291db' (errno: 150)

ALTER TABLE `jonijz1_Test`.`UserGroupLink` 
ADD CONSTRAINT `fk_UserGroupLink_UserGroup`
  FOREIGN KEY (`UserGroupID`)
  REFERENCES `jonijz1_Test`.`UserGroup` (`UserGroupID`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_UserGroupLink_Users1`
  FOREIGN KEY (`UserID`)
  REFERENCES `jonijz1_Test`.`Users` (`UserID`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION

SQL script execution finished: statements: 5 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch  


What am I doing wrong?
And on a sidenote: would a design like this make any sense or am I approaching this wrong as well?
While you seem to have fixed the issue, I will quickly explain why it happened in case anyone finding this will want to understand where the problem was.

When setting foreign keys, the Primary Keys Columns must be of exact same type and attributes. E.g. If you have unsigned attribute on one primary key, you must have it on another. If you have INT data type on one column, then another column must also be INT (NOT TINYINT, MEDIUMINT etc.). 

As you have only one ID set to unsigned, I would go and set it to all IDs. As it is usually good idea to have unsigned attribute on primary keys (if you do not use negative IDs), I would have changed all IDs to have unsigned attribute, as it will improve your query performance.

Also, take a look at what values you can get with various integers (when they are unsigned). WHat you set your lenght  to - does not matter:

Integer Type    Max Value
TINYINT         255
SMALLINT        65535
MEDIUMINT       16777215
INT             4294967295
BIGINT          18446744073709551615