Philip, ----- Original Message ----- From: "Philip Molter" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Thursday, July 18, 2002 6:23 PM Subject: Re: Deleting foreign keys with Innodb tables
> On Thu, Jul 18, 2002 at 03:41:01PM +0300, Victoria Reznichenko wrote: > : Archbold, > : Thursday, July 18, 2002, 12:18:09 AM, you wrote: > : > : A> I'm having a problem dropping a foreign key constraint from an Innodb table. I am using MySQL 3.23.51 for Win32. Any help would be greatly appreciated.... > : > : A> I have a simple set up like the one below: > : > : A> CREATE TABLE mastertable > : A> ( > : A> masterkey INT NOT NULL PRIMARY KEY AUTO_INCREMENT > : A> ) TYPE=innodb; > : > : A> CREATE TABLE othertable > : A> ( > : A> otherkey INT NOT NULL PRIMARY KEY AUTO_INCREMENT > : A> masterkey INT NOT NULL, > : A> INDEX (masterkey), > : A> FOREIGN KEY (masterkey) REFERENCES mastertable (masterkey) ON DELETE CASCADE > : A> ) TYPE=innodb; > : > : Use DROP TABLE and then CREATE TABLE to remove foreign key > : constraints. > > Maybe Heikki can expand on this, because this is really an unacceptable > way to remove a foreign key. What do you do with a table that has > tens or hundreds of millions of rows of data? Do you copy out the > data, recreate the table, then copy it back in? Do you realize > what type of performance/time/service hit that would place on a > database, just to remove a foreign key? ALTER TABLE ... DROP CONSTRAINT FOREIGN KEY ... REFERENCES... is easy to implement but no timetable is set yet. I will next look into a similar problem: dropping or adding an index quickly. Currently MySQL recreates the whole table when you drop or add an index. > Are there any plans to introduce this seemingly basic functionality? > > Philip > > > sql, query > > Please, please, the filter needs to be updated to allow more than > just posts with sql or query in them. I replied to a post on coming > from this list and it was rejected by the query. That's simply > asinine. I can't believe it couldn't even pick out. Please update > the filter with words/phrases such as "innodb", "index", "foreign > key", or "table" so we don't have to keep resending replies to the > list. We sometimes receive Klez.H viruses in emails titled 'A foreign key constraint fails'. The Klez.H worm apparently is the first true artificial intelligence program. Nigerian money laundering letters, which are a daily nuisance, often contain the word 'transaction'. > Either that, or turn the list into subscriber only. I realize that Sorry, but I am not a subscriber :). I read from the news group mailing.database.mysql. > it may discourage some people from asking for help, but it's > definitely more discouraging to have your posts rejected because > you didn't use one of *two* keywords. > > * Philip Molter > * Texas.net Internet > * http://www.texas.net/ > * [EMAIL PROTECTED] Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
