Dropping constraints

epp_b · #1 (**permalink**) 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 · #2 (**permalink**) 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 · #3 (**permalink**) 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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Tracking who is dropping objects	khautinh	SQL Server 2000	1	January 10th, 2007 08:19 PM
"Are you Sure" Before Dropping List in ComboBox	lam2rw	Excel VBA	2	September 22nd, 2006 06:57 AM
How do you suspend constraints?	Aaron Edwards	SQL Server 2005	2	July 31st, 2006 12:16 PM
Rules/Constraints	dhay1999	SQL Server 2000	1	July 11th, 2004 01:50 PM
Tricks with Constraints	ebsutherla	SQL Server 2000	3	June 6th, 2004 05:28 AM