Dropping constraints

epp_b · January 6th, 2005, 04:29 PM

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

Code:

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:

Code:

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):

Code:

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>

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.

js_pandey · January 25th, 2005, 12:04 PM

http://dev.mysql.com/doc/mysql/en/alter-table.html

dig out further into constraint using the link above.
hopefully this will help.

regards,
-Jay

Quote:

quote:Originally posted by epp_b
I need to drop a constraint from a table. The basic structure is this:

Code:

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:

Code:

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):

Code:

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>

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.

-Jay

bohemian · September 12th, 2006, 09:22 PM

Remarkably, to alter a table, MySQL makes a complete copy of the table, does the changes, then drops the old table and renames the new table, so your hope of avoiding "dropping and re-inserting" is moot.

Many errors encountered during the alter are reported as "Error on rename", even though the "rename" is not the problem. To further compound this questionable design, MySQL doesn't behave like normal DB - ie it doesn't automatically drop dependant objects. Eg if you are trying to drop a column, you must first drop any constraints involving that column, or you'll get a "Error on rename" message. MySQL won't ever tell you the "real" reason - you have to guess it for yourself, or look on forums like this one.

The answer is, there is probably be another object somewhere that is dependant on something you are trying to drop - perhaps a view, index, constraint, etc.