Hey All
I have a database with around 40 tables and need to rename every index column.
IE USER a table has a bunch of fields like
user_id | user_username | user_password | etc...
I want to rename the ID columns just to id ie
id | user_username | user_password | etc...
But I keep getting mysql errors on the alter table command ie.
ALTER TABLE database RENAME COLUMN user_id to id;
Plus many different variations.
Whats the best way to do this ?
Hope you can advise
You will have to drop all your foreign key constraints, change the primary keys, then add the constraints again.
By the way, changing the primary keys is a very serious thing, and you should only do it if you really, really need to. This change will break every application that uses your database.
I would not do that if I were you. I would figure out how to change whatever you need to in whatever you are doing before doing that. That is never ever suggested or recommended unless you are the engineer of the database structure or the engineer of the software using it. Doing that is like putting sugar,water,coke & sand in a gas tank and saying, "Gee, what could go wrong?"
Even what the guy said above about dropping constraints and placing them all back. That is all fine and good for the database engineering/structure but even with that done, the applications using them have their own set of problems. Doing a search replace for database column names will not work either. Especially with the command structure you see sometime, you will only mess the code up doing that.
You should be able to do it, for each table, with code like the following (Note: I assume that the column description for each *_id field is INTEGER NOT NULL and that those same fields have AUTO_INCREMENT set. This is pretty normal for an ID column.)
# remove auto_increment, allows us to remove primary key
ALTER TABLE user MODIFY user_id INTEGER NOT NULL;
# remove primary key, allows us to change column name
ALTER TABLE user DROP PRIMARY KEY;
# change column name, re-add auto_increment and primary key
ALTER TABLE user CHANGE user_id id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY;
Heed the other guys' warnings, though. Unless this is your creation (and you've just decided to change naming convention early on in the project or something), changing this will break any other code that expects to interface with this database.
Hope this helps,
Anthony
EDIT: the above assumes that you have no foreign key constraints, where other tables explicitly reference the user.user_id column. If this is not the case, you will have to remove the all of the foreign key constraints before changing the name, then re-add them afterwards. If you're unsure where there are foreign key relationships in your database, you can list them all with the following code (copied from http://www.xinotes.org/notes/note/477/ to improve copy/paste-ability of answer):
SELECT CONCAT(table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name)
FROM information_schema.key_column_usage
WHERE referenced_table_schema = schema()
AND referenced_table_name is not null
ORDER BY table_name, column_name;