MySQL Forums
Forum List  »  Knowledge Base

Dropping foreign keys taking long time
Posted by: John LeBlanc
Date: June 04, 2008 07:38AM

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 T 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;

Options: ReplyQuote


Subject
Views
Written By
Posted
Dropping foreign keys taking long time
3001
June 04, 2008 07:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.