2

I have a database with foreign key constraint where an int(10) is referencing an int(11). I also have int(11) reference int(10).

Their aren't any errors. Will this ever cause problems in the future? (Besides the problem of running out of space for an integer)

I had a kind of related problem when trying to change the column size for a varchar:

mysql 5.6 foreign key constraint error; didn't occur in 5.5

Community
  • 1
  • 1
Chris Muench
  • 17,444
  • 70
  • 209
  • 362
  • see here: http://stackoverflow.com/questions/1632403 and here: http://stackoverflow.com/questions/7552223 and here: http://stackoverflow.com/questions/5562322 –  Oct 14 '13 at 17:20
  • And some of my previous answers on this topic: http://stackoverflow.com/questions/14573451 http://stackoverflow.com/questions/3135804 – Bill Karwin Oct 14 '13 at 17:23

1 Answers1

5

It's no problem. The INT(10) and INT(11) are exactly the same data type (32-bit signed integer) with respect to storage and range of values. The integer argument is only a hint for display.

Also varchar columns of different lengths are okay to use in foreign key relationships. Obviously these do have a different range of values they permit. But it's okay.

Say for example the parent table has a shorter VARCHAR(10) and the child has VARCHAR(20). The child still can't use a string longer than 10 characters, because it must match a string in the parent table, and there simply won't be any longer than 10 characters.

It works in reverse too; if the parent has VARCHAR(20) and the child has VARCHAR(10). The child won't be able to reference longer strings in the parent, but it's never mandatory that the child table reference every value in the parent table, only those it can reference.

Based on this reasoning, you might think that the same logic would apply to integers, so a SMALLINT should be able to reference a BIGINT. But MySQL doesn't permit this.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828