Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    2

    Unanswered: Dropping foreign keys

    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;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2007
    Posts
    2
    The some should actually be most. We are cutting the size of the primary key field from 8 to 4 bytes.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you're only going to do this once, and you already know it takes 40 minutes, then haven't you already done it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •