Mungbeans wrote:
ALTER TABLE `mytable` DROP FOREIGN KEY `keyname`
#1025 - Error on rename of '.\database\mytable' to '.\database\#sql2-6ec-11'
(errno: 152)
ALTER TABLE `mytable` DROP INDEX `keyname`
#1025 - Error on rename of '.\database\#sql-6ec_13' to '.\database\mytable'
(errno: 150
Oh these are fun. Often times this is because adding foreign keys also
adds an index key to the table (on top of the reference key). Why I
don't know, but I'm assuming it adds it for optimization since it knows
you'll be hitting that column (otherwise you wouldn't have created the
reference in the first place). Moving along... what I do first is SHOW
CREATE TABLE `table_name`:
| table_name | CREATE TABLE `table_name` (
`id` int(20) unsigned NOT NULL auto_increment,
`key_column` smallint(5) unsigned default '1',
KEY `key_column` (`key_column`),
CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`key_column`) REFERENCES
`second_table` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
So, first you remove the key:
ALTER TABLE table_name DROP KEY `key_column`;
then the foreign key:
ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`;
Then that should do it. Also note that might also fail if you have,
say, another table foreign key referencing to `key_column`.
Hope this helps, I also wrote this at about 8AM in the morning while
slowly taking in caffeine, so be warned :).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]