Altering table Foreign Keys produces an error (errno:150) (MySQL)

Posted on

Question :

This is the first database I set up so I apologize if my problem is something obvious.

EER Model

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`)
ADD CONSTRAINT `fk_UserGroupLink_Users1`
  REFERENCES `jonijz1_Test`.`Users` (`UserID`)

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?

Answer :

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

One of the PKs had an UNSIGNED flag. Removing that resolved the problem.

We just encountered this error. The issue was that the table was using MyISAM.

Syntax of the SQL is ALTER TABLE t1 ENGINE=InnoDB;

Leave a Reply

Your email address will not be published. Required fields are marked *