I need to drop a constraint from a table. The basic structure is this:

ParentTable (
 ParentID INT,
 ParentName TINYTEXT
);

ChildTable (
 ChildID,
 ParentID,
 ChildName TINYTEXT,

 INDEX(ParentID),
 CONSTRAINT ParentTable_ParentID_fk FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID)
);

Basically, I need to drop the constraint that ChildTable has to ParentTable.

I've tried these commands in pretty well every order I can think of:

ALTER TABLE ChildTable DROP FOREIGN KEY ParentTable_ParentID_fk;
ALTER TABLE ChildTable DROP INDEX ParentID;
ALTER TABLE ChildTable DROP COLUMN ParentID;

And they produce these errors, respectively (regardless of order):

ERROR 1005: Can't create table <oldtable> to <newtable>
ERROR 1025: Error on rename of <oldtable> to <newtable>
ERROR 1025: Error on rename of <oldtable> to <newtable>

Like I said in the title, dropping the tables and re-inserting the data is NOT an option as this is for a large site that is storing a substantial amount of data.

    can you try dropping the index on the fk column, then dropping the fk? failing that can you disable the fk?

      can you try dropping the index on the fk column, then dropping the fk?

      OK....doesn't work. Like I said, I've tried it every combination possible.

      failing that can you disable the fk?

      how?

        a couple of options here i can think of

        create an empty InnoDB table with identical definitions, but not the FK constraint and insert the rows with INSERT INTO ... SELECT * FROM ....

        this might also be relevant

        During the conversion of big tables, you should increase the size of the InnoDB buffer pool to reduce disk I/O. Do not use more than 80% of the physical memory, though. You can also increase the sizes of the InnoDB log files and the log files.

        to disable the fk constraints

        (http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html)
        set FOREIGN_KEY_CHECKS to 0 as of MySQL 4.1.1. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. For earlier versions, you can disable the variable manually within mysql when loading the dump file like this:

        mysql> SET FOREIGN_KEY_CHECKS = 0;
        mysql> SOURCE dump_file_name
        mysql> SET FOREIGN_KEY_CHECKS = 1;

        This allows you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys. It also speeds up the import operation. FOREIGN_KEY_CHECKS is available starting from MySQL 3.23.52 and 4.0.3.

        Setting FOREIGN_KEY_CHECKS to 0 can also be useful for ignoring foreign key constraints during LOAD DATA operations.

        InnoDB does not allow you to drop a table that is referenced by a FOREIGN KEY constraint, unless you do SET FOREIGN_KEY_CHECKS=0. When you drop a table, the constraints that were defined in its create statement are also dropped.

          Nope, doesn't work 🙁

          I get the same results after using [FONT=courier new]SET FOREIGN_KEY_CHECKS = 0;[/FONT].

          BTW, I'm using MySQL 4.0.12

            Write a Reply...