CODEWITHSUNDEEP
mysqlforeign-keyskeyintbigint
Pedro Araujo Jorge
Pedro Araujo Jorge

Reputation: 686

How to change bigint to int in mysql, with table having foreign keys

I recently noticed that there are several bigint type fields in one of my databases that could be replaced with type "int". The problem is that the database is already online and in use and also that there are foreign keys in action, so when I try changing the data type in the offline DB, mysql won't let me and produces the error message: "#1025 - Error on rename of (...)".

So how can I change these fields while keeping the foreign keys happy? (and not wiping out the existing DB!)

Upvotes: 3

Views: 4052

Answers (3)

Tom
Tom

Reputation: 31

You can alter a column from BIGINT to INT as long as there is not any data in the column that would not fit into the smaller INT datatype.

ALTER TABLE mytable MODIFY COLUMN mycolumn INT(11);

Upvotes: 3

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

You could try:

  1. Add new columns with proper types.
  2. Copy values from old columns to new columns.
  3. Rename columns.
  4. Update constrains (maybe in two steps -- DROP and CREATE).
  5. Drop old columns.

Upvotes: 0

Bhrugesh Patel
Bhrugesh Patel

Reputation: 1106

You can't reduce the size of field when it holds data. You can only expand them. What you can do is export all the data, disable the keys, empty the tables, change the field sizes & import the data again.

Upvotes: -1

Related Questions