Renaming a column without modifying its properties?

[kaisellgren]

Hi,

MySQL's way to rename a column is: ALTER TABLE table CHANGE columnold columnnew constraints;

How would I remain the constrains? I do not want to specify lets say tinyint unsigned not null auto_icnrement. Is there a way to 'grab' those details and put it in the clause? I have a script that occasionally modifies the column name, however, the script can not know the details about the column, just the name. So is there a way in MySQL to select (or something) the details and use the same for the new column?

Thanks for help!

[Fumigator]

Are you changing column names as part of a regular process? You'd probably be better off just creating a view using the new column names.

[kaisellgren]

Originally Posted by Fumigator

Are you changing column names as part of a regular process? You'd probably be better off just creating a view using the new column names.

This is complicated...

I am coding a software which supports MySQL, PostgreSQL, SQLite, DB2, MaxDB, Oracle, Firebird and SQL Server.

So the SQL HAS to be working with all those DBMS. Of course it will not be, but my application will handle it. I have already done the column renaming in most of those DBMS, and right now I am trying to make it for MySQL, too. But then I found that I need to enter column info along with the modification of the column name which sucks.

And no I can not use views. The renaming of a column happens randomly, like every once a month.

EDIT: I was hoping to see some kind of code that SELECTs the column info into a var or somewhere, then puts it in the clause ALTER TABLE table MODIFY old new _info_in_a_var

[CFMaBiSmAd]

Right from the ALTER table section in the mysql manual -

If you use ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name.

And after a table is created with valid working column names, there should be no regular need to change them.

[kaisellgren]

Originally Posted by CFMaBiSmAd

Right from the ALTER table section in the mysql manual -

And after a table is created with valid working column names, there should be no regular need to change them.

But I am renaming column(s), not table(s)...

Does RENAME TO work for columns too?

[CFMaBiSmAd]

Opps, read that too fast.

You would need to list the current properties in the statement.

Edit: A tool like Mysql Administrator does it this way -

ALTER TABLE `db_name`.`table_name` CHANGE COLUMN `name1` `name2` current_definition_here;

[Fumigator]

If this is being done programmatically then you should be able to parse the results of SHOW CREATE TABLE to get the constraint details and concantenate that string data into the ALTER TABLE query. Seems like a lot of trouble to me though.

[kaisellgren]

Originally Posted by Fumigator

If this is being done programmatically then you should be able to parse the results of SHOW CREATE TABLE to get the constraint details and concantenate that string data into the ALTER TABLE query. Seems like a lot of trouble to me though.

Thanks for the idea! I'll definetly use that if I do not find anything else.

[kaisellgren]

Originally Posted by CFMaBiSmAd

Opps, read that too fast.

You would need to list the current properties in the statement.

Edit: A tool like Mysql Administrator does it this way -

ALTER TABLE `db_name`.`table_name` CHANGE COLUMN `name1` `name2` current_definition_here;

But umm... does "current_definition_here" refer to the current definition? I have no idea about the column structure, I need the code (preferably SQL) to do it...

[CFMaBiSmAd]

Fumigator's suggestion is the same thing I would have suggested.