Sorry, my bad! Must learn to read the whole message!!
This can be caused because when a foreign key is created mysql adds an index key to the column in addition to the foreign key. Why I'm not sure, but I'm guessing its for performance. To drop this foreign key First do this to get the index names SHOW CREATE TABLE `table_name` Which will give an output something like this | table_name | CREATE TABLE `table_name` ( `id` int(10) unsigned NOT NULL auto_increment, `idx` int(5) unsigned NOT NULL, KEY `key_column` (`idx`), CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`idx`) 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 `idx`; then the foreign key: ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`; Then that should do it! Regards John Daisley Microsoft SQL Server 2005/2008 Database Administrator Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk On 22 July 2010 14:26, Victor Subervi <victorsube...@gmail.com> wrote: > On Thu, Jul 22, 2010 at 8:47 AM, John Daisley <daisleyj...@googlemail.com > > wrote: > >> Most likely a foreign key constraint would be violated if the table were >> dropped. Check those index definitions on `Store` and `User` columns. >> > > But I don't want to drop the table, I want to drop the foreign key on > Store. How? > TIA, > V > >> >> Regards >> >> John Daisley >> >> Microsoft SQL Server 2005/2008 Database Administrator >> Certified MySQL 5 Database Administrator >> Certified MySQL 5 Developer >> Cognos BI Developer >> >> Telephone: +44 (0)7918 621621 >> Email: john.dais...@butterflysystems.co.uk >> >> On 22 July 2010 14:02, Victor Subervi <victorsube...@gmail.com> wrote: >> >>> Hi; >>> >>> mysql> alter table personalData drop foreign key Store; >>> ERROR 1025 (HY000): Error on rename of './test/personalData' to >>> './test/#sql2-14ce-a61' (errno: 152) >>> mysql> describe personalData; >>> >>> +-------------------+------------------+------+-----+------------+----------------+ >>> | Field | Type | Null | Key | Default | >>> Extra | >>> >>> +-------------------+------------------+------+-----+------------+----------------+ >>> | ID | int(10) unsigned | NO | PRI | NULL | >>> auto_increment | >>> | Store | varchar(40) | NO | MUL | NULL >>> | | >>> | User | varchar(50) | NO | MUL | NULL >>> | | >>> | FirstName | varchar(100) | NO | | NULL >>> | | >>> | LastName | varchar(100) | NO | | NULL >>> | | >>> | Phone | varchar(13) | YES | | NULL >>> | | >>> | Cell | varchar(13) | YES | | NULL >>> | | >>> | Fax | varchar(13) | YES | | NULL >>> | | >>> | AddressID | int(11) | YES | | NULL >>> | | >>> | ShippingAddressID | int(11) | YES | | NULL >>> | | >>> | DOB | date | YES | | 2000-01-01 >>> | | >>> | Email | varchar(100) | NO | | NULL >>> | | >>> | PW | varchar(12) | NO | | NULL >>> | | >>> >>> +-------------------+------------------+------+-----+------------+----------------+ >>> 13 rows in set (0.00 sec) >>> >>> How drop it? >>> TIA, >>> Victor >>> >> >> >> >> -- >> John Daisley >> >> Certified MySQL 5 Database Administrator >> Certified MySQL 5 Developer >> Cognos BI Developer >> >> Telephone: +44 (0)7918 621621 >> Email: john.dais...@butterflysystems.co.uk >> > > -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
