Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

www.xxx.con, linux raid 10 near far offset comparison, xxxCons.jsp, w2ksp4 download, linux mdadm use spare drive, www.xxxcon, wwwxxxx.ocB, www...xxxcon, wwwxxxxcoN, Wwwxxx.con

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