Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

WwWXxX, udp high ports, d-link extract firmware dsl-2750u, wwwwxxxxx.2G, yxxxcom, WWWXXX, ftp://192.168.100.100, wwwxxx 100, www.xxxcon, wwwxxx

Links

XODOX
Impressum

#1: Deleting Foreign Key

Posted on 2007-01-02 00:56:36 by Mungbeans

I have a foreign key in a table which I need to drop. I have successfully
dropped other foreign keys from this table using phpMyAdmin. However when I
use these commands I get similar error messages:


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)

Any clues as to what I need to do to get rid of this constraint? I want to
delete the entire table structure and replace it with a different one and
this is the only thing stopping me.


--
View this message in context: http://www.nabble.com/Deleting-Foreign-Key-tf2905915.html#a8 118367
Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Report this message

#2: Re: Deleting Foreign Key

Posted on 2007-01-04 15:08:05 by Heikki Tuuri

Mungbeans,

I do not understand how you get error 152 from the ALTER.

../include/my_base.h:355:#define HA_ERR_ROW_IS_REFERENCED 152

Please print SHOW INNODB STATUS\G after you get that error.

Best regards,

Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php

..............

From: Mungbeans Date: January 2 2007 12:56am
Subject: Deleting Foreign Key

Get Plain Text

I have a foreign key in a table which I need to drop. I have successfully
dropped other foreign keys from this table using phpMyAdmin. However when I
use these commands I get similar error messages:


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)

Any clues as to what I need to do to get rid of this constraint? I want to
delete the entire table structure and replace it with a different one and
this is the only thing stopping me.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Report this message

#3: Re: Deleting Foreign Key

Posted on 2007-01-04 17:20:03 by chriswhite

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/mysql?unsub=gcdmg-mysql@m.gmane.org

Report this message

#4: Re: Deleting Foreign Key

Posted on 2007-01-05 03:14:34 by Mungbeans

Chris White-4 wrote:
>
> Moving along... what I do first is SHOW
> CREATE TABLE `table_name`:
> ...snipped...
> 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`;
> ...snipped...
>

Thank you Chris. The SHOW CREATE TABLE `table_name` was just the ticket.
It showed both the key name and the foreign key name. The foreign key
hadn't shown up in phpMyAdmin or MySQLAdministrator - all very frustrating.

I dropped the foreign key first and then the index.

I'm sure leaving the work until after my own caffeine boost also helped
considerably!

Cheers!


--
View this message in context: http://www.nabble.com/Deleting-Foreign-Key-tf2905915.html#a8 171880
Sent from the MySQL - General mailing list archive at Nabble.com.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql@m.gmane.org

Report this message