This week's book giveaway is in the
We're giving away four copies of
Arduino in Action
and have Martin Evans, Joshua Noble, and Jordan Hochenbaum on-line!
A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
Win a copy of
Arduino in Action
this week in the
A special promo:
Enter your blog post or vote on a blogger to be featured in an upcoming Journal
How to drop a foreign_key column from a table also with data that is existing, Mysql?
Joined: May 23, 2008
Dec 22, 2008 04:54:00
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.
Joined: Oct 13, 2005
Dec 22, 2008 05:57:00
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.
Joined: Oct 06, 2007
Dec 22, 2008 09:20:00
alter table <table_name> DROP FOREIGN KEY <constraint>;
SET FOREIGN_KEY_CHECKS = 0; FLUSH PRIVILEGES; -- don't forget this one
If it still is an issue and it is because your foreign key is also a key in the table, too:
ALTER TABLE <table_name> DROP KEY <key>; alter table <table_name> DROP FOREIGN KEY <constraint>;
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:
- it saves me about five hours per week
subject: How to drop a foreign_key column from a table also with data that is existing, Mysql?
Retrieve MySQL table column names
migration from MySql 4.1 to MySql 5.0
Problem in getting data
Database and jsp
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2013