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
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;