Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jan 2006
    Location
    Finland, Hollola
    Posts
    285
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Renaming a column without modifying its properties?

    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!
    PHP 5 & MySQL 5 (Y)

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #3
    Regular Coder
    Join Date
    Jan 2006
    Location
    Finland, Hollola
    Posts
    285
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    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
    PHP 5 & MySQL 5 (Y)

  • #4
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,007
    Thanks
    2
    Thanked 311 Times in 303 Posts
    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.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #5
    Regular Coder
    Join Date
    Jan 2006
    Location
    Finland, Hollola
    Posts
    285
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    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?
    PHP 5 & MySQL 5 (Y)

  • #6
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,007
    Thanks
    2
    Thanked 311 Times in 303 Posts
    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 -

    Code:
    ALTER TABLE `db_name`.`table_name` CHANGE COLUMN `name1` `name2` current_definition_here;
    Last edited by CFMaBiSmAd; 09-25-2008 at 07:53 PM.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

  • #8
    Regular Coder
    Join Date
    Jan 2006
    Location
    Finland, Hollola
    Posts
    285
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fumigator View Post
    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.
    PHP 5 & MySQL 5 (Y)

  • #9
    Regular Coder
    Join Date
    Jan 2006
    Location
    Finland, Hollola
    Posts
    285
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    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 -

    Code:
    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...
    PHP 5 & MySQL 5 (Y)

  • #10
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,007
    Thanks
    2
    Thanked 311 Times in 303 Posts
    Fumigator's suggestion is the same thing I would have suggested.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •