MySQL Bugs: #36288: foreign key type modification not reflected in related table

Bug #36288	foreign key type modification not reflected in related table
Submitted:	23 Apr 2008 12:44	Modified:	12 Jan 2010 16:13
Reporter:	[ name withheld ]	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.19, 5.0.17 OSS RC Revision 2972	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	foreign key, type modification

Description:
Let table1.tab2ID be a foreign key with destination table2.tab2ID. A relation in between them is already defined.
When modifying the data type of column table2.tab2ID, this modification is not taken over in table1.tab2ID, leaving the model in an inconsistent state.
(You will realize this latest when running the exported SQL CREATE script.)

How to repeat:
1. Create a diagram
2. Using the Diagram, create two tables with primary key of data type UNSIGNED BIGINT.
3. Create a non identifying foreign key relation in between them (i.e. a new column will be auto generated with the correct data type UNSIGNED BIGINT)
4. Modify the data type of the destination column (e.g. modify from UNSIGNED to SIGNED)
5. check what data type the auto generated column has (it will be the old, unmodified data type, which is an inconsistency according to the existing relation)

Suggested fix:
When a column's data type is modified, all foreign key constraints shall be walked through and the respective columns shall be updated to have the same data type.
In case a column's data type shall be modified, which has a foreign key constraint, this modification should be rejected or a special message should allow to confirm this modification.

Thank you for a problem report. The problem is even more obvious when INT primary key is replaced with VARCHAR(20), for example, but foreign keys referencing it remains INT.

this is not a feature of workbench 5.0 - the user has to take care of changes like this. In Standard Edition though, theres a validation module that checks for such (and several other) inconsistencies and outputs a warning.
Because we just didn't implement such feature I wouldn't call it actually a bug, but I'm setting this to 'to be fixed later' rather than 'not a bug' because we might consider to add this in a future version.

I cant wait to have this feature included. May be implement it in such a way that it can be turned on and off through preferences.

Please, check with a newer version, 5.2.10, and inform about the results.

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".