-1

What would be the DDL syntax for changing a MySQL column name from int(10) to int(11)?

I've done a number of searches using DDL, int(10) and int(11) like this but I've not found any syntax examples.

The current migration is failing because an int(11) is being used as a foreign key referencing an int(10)

I realise I can do this in a GUI. I'm looking to push this change into a migration script for Django.

This was marked as a duplicate because another answer generally solved the issue. I was searching specifically for changing int(10) to int(11). More to the point ... the question marked as a possible duplicate did not come up with the searches I was making.

Keith John Hutchison
  • 4,955
  • 11
  • 46
  • 64
  • 1
    Incidentally, the number in parentheses after an INT is almost meaningless and can be omitted (although MySQL will, annoyingly, quietly add '11' if you do) – Strawberry Mar 23 '19 at 08:00
  • It is not meaningless in the context of a foreign key. Example. 2019-03-23 12:51:59 7000064f4000 Error in foreign key constraint of table bd2l_api/#sql-6f3_68: FOREIGN KEY (client_id) REFERENCES bd2l_client (id): 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. – Keith John Hutchison Mar 23 '19 at 18:02
  • My point is, if you had omitted it from both definitions, the problem would not have arisen. – Strawberry Mar 23 '19 at 18:31
  • In theory, the point is valid. In practice when dealing with legacy databases, it is not. The problem I faced was an existing database had int(10) in an old table and int(11) in the new table. It is immaterial that int(10) and int(11) store the same number as long as MySQL insists that foreign key constraints won't work with them. – Keith John Hutchison Mar 23 '19 at 18:40

1 Answers1

1

A simple ALTER TABLE:

ALTER TABLE yourtable MODIFY COLUMN yourcolumn INT(11)

Demo on dbfiddle

Nick
  • 138,499
  • 22
  • 57
  • 95