Bug #35875 Inconsistent col. definitions from FKEY constraint are not checked in validation
Submitted: 7 Apr 2008 10:52 Modified: 28 May 2008 14:29
Reporter: Daniel Haas Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.16a RC rev 2911 OS:Windows (Vista)
Assigned to: CPU Architecture:Any

[7 Apr 2008 10:52] Daniel Haas
Description:
When you have a foreign key constraint involving two integer columns where one is defined unsigned and the other one not, the MySQL Validation does not complain. However such a model is not forward engineerable and produces the following error:

Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

How to repeat:
1. Create table one with an unsigned auto_increment primary key
2. Create a second table with a an signed integer column
3. Create fkey relationship to the first table from the column in the second table
4. Run the (MySQL) Validatation -> No errors are shown
5. Try to forward engineeer the model: It will fail with the above mentioned error

Suggested fix:
A check should be implemented in the validation to check this kind of errors so that the user may see the error befor trying to generate the sql and then wondering why it fails.
[7 Apr 2008 11:50] MySQL Verification Team
Thank you for the bug report. Could you please provide the model file project
which presents that behavior?. Thanks in advance.
[7 Apr 2008 16:02] Daniel Haas
Example file created by following the step by step guide, which shows the bug.

Attachment: different-column-types-bug.mwb (application/octet-stream, text), 3.83 KiB.

[7 May 2008 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[8 May 2008 10:38] Daniel Haas
Since I do not own a SE version currently, I cannot test this now.
I will do it when I have an SE version again.
[28 May 2008 14:29] MySQL Verification Team
Thank you for the feedback. I can't repeat with version 5.0.21 please upgrade.
Thanks in advance.