{"id":448,"date":"2019-10-19T17:12:10","date_gmt":"2019-10-19T10:12:10","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=448"},"modified":"2020-04-11T23:24:00","modified_gmt":"2020-04-11T16:24:00","slug":"mariadb-alter-table","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-alter-table\/","title":{"rendered":"MariaDB Alter Table"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB alter table<\/code> statement to add a column, alter a column, rename a column, drop a column and rename a table.<\/p>\n\n\n\n

Setting up a sample table<\/h2>\n\n\n\n

The following statement creates a new table<\/a> called customers<\/code> for demonstration:<\/p>\n\n\n

create<\/span> table<\/span> customers(\n    id<\/span> int<\/span> auto_increment,\n    name<\/span> varchar<\/span>(255<\/span>) not<\/span> null<\/span>,\n    created_at timestamp<\/span> default<\/span> current_timestamp<\/span>,\n    is_active bool<\/span> not<\/span> null<\/span> default<\/span> false<\/span>,\n    primary key<\/span>(id<\/span>)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Using MariaDB alter table to add a column or columns to a table<\/h2>\n\n\n\n
The alter table add<\/code> statement allows you to add one or more columns to a table.<\/p>\n\n\n\n
1) Add a column to a table<\/h3>\n\n\n\n
To add a column to a table, you use the alter table add<\/code> syntax:<\/p>\n\n\n
alter<\/span> table<\/span> table_name\nadd<\/span> \n    new_column_name column_definition\n    <\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n
[first | after column_name]<\/p>\n\n\n\n
In this syntax:<\/p>\n\n\n\n
  • First, specify the name of the table that you want to add a column after the alter table<\/code> keywords.<\/li>
  • Second, specify the name of the new column after the add<\/code> keyword.<\/li>
  • Third, specify the datatype, maximum size, and column constraint of the new column.<\/li>
  • Finally, specify the position of the new column in the table. It is possible to add a column after an existing column (after column_name<\/code>) or as the first column (first<\/code>). If you skip this option, MariaDB will append the new column at the end of the column list.<\/li><\/ul>\n\n\n\n
    This example uses the alter table add<\/code> statement to add a column at the end of the customers<\/code> table:<\/p>\n\n\n
    alter<\/span> table<\/span> customers\nadd<\/span> email varchar<\/span>(255<\/span>) not<\/span> null<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    To show the column list of the customers<\/code> table, you use the describe<\/code> statement:<\/p>\n\n\n
    describe<\/span> customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MariaDB<\/figure>\n\n\n\n
    2) Add multiple columns to a table<\/h3>\n\n\n\n
    The following syntax allows you to add multiple columns to a table:<\/p>\n\n\n
    alter<\/span> table<\/span> table_name\n    add<\/span> new_column_name column_definition\n    [first<\/span> | after<\/span> column_name],\n    add<\/span> new_column_name column_definition\n    [first<\/span> | after<\/span> column_name],\n    ...;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    For example, this statement adds two columns phone<\/code> and address<\/code> to the customers<\/code> table:<\/p>\n\n\n
    alter<\/span> table<\/span> customers\nadd<\/span> phone varchar<\/span>(15<\/span>),\nadd<\/span> address varchar<\/span>(255<\/span>);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Here is the new structure of the customers<\/code> table:<\/p>\n\n\n
    describe<\/span> customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MariaDB<\/figure>\n\n\n\n
    Using MariaDB alter table to modify columns<\/h2>\n\n\n\n
    1) Modify a column<\/h3>\n\n\n\n
    To change the characteristics of a column, you use the alter table<\/code> modify statement:<\/p>\n\n\n
    alter<\/span> table<\/span> table_name\nmodify<\/span> column_name column_definition\n[ first<\/span> | after<\/span> column_name];    \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Suppose that you want to modify the phone<\/code> column to a not null<\/code> column whose type is varchar<\/code> with the maximum size 20<\/p>\n\n\n\n
    First, display the columns of the customers<\/code> table:<\/p>\n\n\n
    describe<\/span> customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"\"<\/figure>\n\n\n\n
    Then, modify the phone<\/code> column:<\/p>\n\n\n
    alter<\/span> table<\/span> customers \nmodify<\/span> phone varchar<\/span>(20<\/span>) not<\/span> null<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Finally, show the column list of the customers<\/code> table again to verify the modification:<\/p>\n\n\n
    describe<\/span> customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MariaDB<\/figure>\n\n\n\n
    2) Modify multiple columns<\/h3>\n\n\n\n
    To modify multiple columns, you use the following syntax:<\/p>\n\n\n
    alter<\/span> table<\/span> table_name\n    modify<\/span> column_name column_definition\n    [ first<\/span> | after<\/span> column_name],\n    modify<\/span> column_name column_definition\n    [ first<\/span> | after<\/span> column_name],\n    ...;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    First, show the column list of the customers<\/code> table:<\/p>\n\n\n
    describe<\/span> customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MariaDB<\/figure>\n\n\n\n
    Then, modify the email<\/code> and address<\/code> columns:<\/p>\n\n\n
    alter<\/span> table<\/span> customers \nmodify<\/span> email varchar<\/span>(255<\/span>),\nmodify<\/span> address varchar<\/span>(255<\/span>) after<\/span> name<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Finally, display the new column list of the customers<\/code> table to verify the changes:<\/p>\n\n\n
    describe<\/span> customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    \"MariaDB<\/figure>\n\n\n\n
    Using MariaDB alter table to rename a column in a table<\/h2>\n\n\n\n
    To rename a column, you use the following syntax:<\/p>\n\n\n
    alter<\/span> table<\/span> table_name\nchange<\/span> column<\/span> original_name new_name column_definition\n[first<\/span> | after<\/span> column_name];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this syntax:<\/p>\n\n\n\n
    • First, specify the name of the table from which you want to rename the column after the alter table<\/code> keywords.<\/li>
    • Second, specify the name of the column and the new name followed by the column definition after the change column<\/code> keywords.<\/li>
    • Third, specify the new position of the column by using the first<\/code> or after column_name<\/code> clause.<\/li><\/ul>\n\n\n\n
      This example uses alter table change column<\/code> statement to rename the column address<\/code> to office_address<\/code>:<\/p>\n\n\n
      alter<\/span> table<\/span> customers\nchange<\/span> column<\/span> address office_address varchar<\/span>(255<\/span>) not<\/span> null<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      The following statement shows the new column list of the customers<\/code> table:<\/p>\n\n\n
      describe<\/span> customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      \"MariaDB<\/figure>\n\n\n\n
      Using MariaDB alter table to drop a column<\/h2>\n\n\n\n
      To drop a column in a table, you use the alter table drop column<\/code> statement:<\/p>\n\n\n
      alter<\/span> table<\/span> table_name\ndrop<\/span> column<\/span> column_name;    \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      In this syntax:<\/p>\n\n\n\n
      • First, specify the name of the table that you want to drop a column or columns after the alter table<\/code> keywords.<\/li>
      • Second, specify the name of the column that you want to drop after the drop column<\/code> keywords.<\/li><\/ul>\n\n\n\n
        The following example removes the office_address<\/code> column from the customers<\/code> table:<\/p>\n\n\n
        alter<\/span> table<\/span> customers\ndrop<\/span> column<\/span> office_address;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
        Using MariaDB alter table statement to rename a table<\/h2>\n\n\n\n
        To rename a table, you use the alter table rename to<\/code> statement:<\/p>\n\n\n
        alter<\/span> table<\/span> table_name\nrename<\/span> to<\/span> new_table_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
        In this syntax:<\/p>\n\n\n\n
        • First, specify the name of the table that you want to rename after the alter table<\/code> keywords.<\/li>
        • Second, specify the new table name after the rename to<\/code> keywords.<\/li><\/ul>\n\n\n\n
          The following statement renames the table customers<\/code> to clients<\/code>:<\/p>\n\n\n
          alter<\/span> table<\/span> customers \nrename<\/span> to<\/span> clients; \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
          In this tutorial, you learned how to use the MariaDB alter table<\/code> statement to add a column, modify a column, rename a column, drop a column, and rename a table.<\/p>\n
          \n\t
          \n\t\t
          Was this tutorial helpful ?<\/div>\n\t\t
          \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
          \n\t\t
          \n\t\t\t
          <\/div>\n\t\t\t\n\t\t\t