MySQL Performance: MySQL => Changing datatypes of foreign keys

Members

Help

Home

Home » Performance » MySQL » Changing datatypes of foreign keys

Show: Today's Messages :: Show Polls :: Message Navigator

Changing datatypes of foreign keys [message #1967]

Sat, 29 September 2007 20:21

omakase
Messages: 3
Registered: June 2007

Junior Member

I want to change the data type of all my id fields in my (InnoDB) schema from mediumints to ints. The problem is there are a lot of foreign key references and I can't figure out a simple way to change the type other than removing all he foreign key constraints, changing the columns and adding all the foreign key constraints again.

Is there an easier way?

Report message to a moderator

Re: Changing datatypes of foreign keys [message #2035 is a reply to message #1967 ]

Mon, 08 October 2007 18:22

danciulinaru
Messages: 4
Registered: October 2007
Location: Las Vegas

Junior Member

Step 1:
mysql> SET FOREIGN_KEY_CHECKS=0;

Step 2:
mysql> ALTER TABLE table_name modify column data type;

Step3:
mysql> SET FOREIGN_KEY_CHECKS=1;

Basically, is what said, but instead of removing fk, you're just disabling them.

Report message to a moderator

Previous Topic:	Any suggestions to tackle a slow query on a large table with composite PK?
Next Topic:	Any ideas for performance optimization?

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 5 15:33:14 EDT 2009

Total time taken to generate the page: 0.01237 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.