warn about incompatible datatypes (MySQL errno: 150)

[ondras]

From martin.meridius on March 03, 2014 20:39:56

Hi,
I just spent some time googling what "errno: 150" in MySQL is. As it turned out I forgot to change the default TINYINT type of one of my indexes to matching INT type in other foreign key.

Would it be possible to change the default type from TINYINT to more usable INT type? Only 256 unsigned for key is really low number.

Or better yet, when exporting (generating SQL) the Designer should at least warn about incompatible datatypes.

Thanks,
Martin

Original issue: http://code.google.com/p/wwwsqldesigner/issues/detail?id=180

[ondras]

From ondrej.zara on March 06, 2014 23:39:05

Hi Martin,

I hope I fixed this in https://code.google.com/p/wwwsqldesigner/source/detail?r=00a847e101f4690fa8ae123d9ed1f44546858e46 .

Status: Fixed

[ondras]

From martin.meridius on March 07, 2014 11:38:55

Hi Ondra,

thank you for the fix, although warning about incompatible datatypes would be still cool. ;)

[ondras]

From ondrej.zara on March 10, 2014 05:01:08

Can you please elaborate a bit? I am not sure what types are incompatible and/or whether a FK value can be - in some circumstances - of a different type than the PK.

[ondras]

From martin.meridius on March 10, 2014 16:11:22

That's the thing, AFAIK you can't set up foreign key between two keys of different type. Not even two numeric types as Integer and Tinyint are compatible. And without warning during exporting and/or saving, you'll notice that after you ran that query through database.

[ondras]

From martin.meridius on March 10, 2014 16:16:53

So to sum it up, simple checking if connected keys are of the same type upon saving/exporting (generating SQL) would suffice. If they have different type, the app would alert user. And this behavior could be optional.

[ondras]

From ondrej.zara on March 15, 2014 02:17:43

Sounds like a good idea to me.

Status: Accepted

[ondras]

From ondrej.zara on March 15, 2014 02:18:41

Owner: ondrej.zara