my dog learned polymorphism*
The moose likes JDBC and the fly likes How to drop a foreign_key column from a table also with data that is existing, Mysql? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to drop a foreign_key column from a table also with data that is existing, Mysql?" Watch "How to drop a foreign_key column from a table also with data that is existing, Mysql?" New topic
Author

How to drop a foreign_key column from a table also with data that is existing, Mysql?

prabhu pandurangan
Ranch Hand

Joined: May 23, 2008
Posts: 120
Hi Members,

I have to delete a column from a table, which is a foreign_key. Also the column has some data with it. I need to drop the column along with the data.
Im using mysql 5.0.27.

Please share your views in this. How it shall be done.

I also tried using 'set foreign_key_checks = 0;' But im facing some exceptions:

Error: 150, error on rename.

Regards,
Prabhu.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38481
    
  23
If it is a foreign key, you are supposed to keep the data, particularly if it is labelled ON DELETE RESTRICT. You would have to drop all tables using that column first, before dropping the column.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338


or



If it still is an issue and it is because your foreign key is also a key in the table, too:



If that doesn't work... you will need to do what Campbell suggests... but you will need to unload your table and reload it after removing your column(s).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to drop a foreign_key column from a table also with data that is existing, Mysql?