[jleblanc24]

I need to update our schema to change the definition of some of the primary keys. I am dropping the foreign key constraints but it is taking about 40 minutes to finish. I have enclosed the code I am using. Does anyone know a way to speed this up or a better way to do it?

DECLARE table_crsr cursor for
SELECT table_name, CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS where
table_schema = 'spa' and constraint_type = 'FOREIGN KEY';

SET FOREIGN_KEY_CHECKS = 0;

-- Open the cursor
open table_crsr;

-- Get first table
fetch next from table_crsr into tableName, cName;

-- Loop thru tables
WHILE (done = 0) DO

-- Build the alter table statement
set @query = CONCAT('alter table ' ,tableName, ' drop foreign key ', cName);
PREPARE cCommand FROM @query;

-- Execute the command
EXECUTE cCommand;

-- Get next table
fetch next from table_crsr into tableName, cName;

END WHILE;

[r937]

my question is, if you are changing the definition of only some of the primary keys, why do you need to obliterate all foreign keys in the database?

why not just drop only the foreign keys that reference the primary keys that are changing?

[jleblanc24]

The some should actually be most. We are cutting the size of the primary key field from 8 to 4 bytes.

[r937]

if you're only going to do this once, and you already know it takes 40 minutes, then haven't you already done it?