Modify an existing MySQL column

The best laid plans of mice and DBAs oft go awry, so it is sometimes necessary to change the characteristics of a column after it exists and contains data. Beware whenever you make changes to your database — always make a backup first.


After a week of using the contacts table created in Create a basic MySQL table, we may find that 40 characters for the column name doesn’t cut it. To increase the size of the name column to 80 characters:

ALTER TABLE contacts CHANGE name name VARCHAR(80);

The first part of this statement (ALTER TABLE contacts CHANGE name) identifies that we want to change the column name in the table contacts. The second part of this statement (name VARCHAR(80)) redefines the column name. We could further define this column as NOT NULL, for example, with

ALTER TABLE contacts CHANGE name name VARCHAR(80) NOT NULL;

 

About Quinn McHenry

Quinn was one of the original co-founders of Tech-Recipes. He is currently crafting iOS applications as a senior developer at Small Planet Digital in Brooklyn, New York.
View more articles by Quinn McHenry

The Conversation

Follow the reactions below and share your own thoughts.

  • http://www.niyum.com Sitthykun Cambodia

    good code

  • sql

    thanks…but i m not getting result….sorry..:’-(

    • nutharsh

      > One important pre-requisite before running the second SQL command to make a column to hold not-null values is that it should not contain any null values populated previously. so, run a query to remove null values and then try the second SQL command.

  • bhaskar

    It’s really simple and superb.

  • Wpabitha

    thanks

  • Siyeni

    Thanks, I have no regrets hitting this page. Good job.

  • http://www.sunganani.com Sunganani

    Thanks man

  • Cclark Home

    THANK YOU! I’m just learning mysql and have done a lot of drop tables due to not being able to figure this out.

  • Ramboraju5

    CHANGE didn’t work for me.. but the below code worked for me on MySQL
    ALTER TABLE contacts MODIFY name VARCHAR(80);

  • Kultar Singh

    thanx for the information……

  • http://www.facebook.com/abdulbaasit.ismail Abdul-Baasit Ismail

    worked great. . . .a timeless answer obviously

  • surendra

    nice code really ..thank you.

  • pankaz kumar

    really nice….good work…thankx

  • M.jan

    hi dear :
    although i write this command alter table [name of table]modify name varcahr(65); i can’t get the result
    name is existed in my table kindly help me what can i do?

  • vipul

    A simple query was made very difficult in mysql original documentation, thanx a lot mahn!!