Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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`)
  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?

share|improve this question

2 Answers 2

up vote 0 down vote accepted

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
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.