Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
| Search | Today's Posts | Mark Forums Read
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 6th, 2005, 04:29 PM
Registered User
 
Join Date: Jun 2004
Location: , , .
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dropping constraints

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.

  #2 (permalink)  
Old January 25th, 2005, 12:04 PM
Authorized User
 
Join Date: Jan 2005
Location: Delhi, Delhi, India.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to js_pandey Send a message via Yahoo to js_pandey
Default

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
  #3 (permalink)  
Old September 12th, 2006, 09:22 PM
Registered User
 
Join Date: Sep 2006
Location: Melbourne, VIC, Australia.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.