Monday 9 December 2019

MySQL ALTER TABLE Commands: How to Add, Delete, & Change Columns

MySQL (and MariaDB) allows you to change the structure of tables with the ALTER TABLE SQL command. Using the command, you can easily change the name of your table and columns, add or delete columns, or change the type of existing columns. Let’s see this in action.

Uses and Examples of ALTER TABLE

First, let’s create a database and a table, which we’ll respectively name “test” and “employees.” Here’s how you’d do that:

Rename a Table

The syntax for renaming a table is:
So, to change our employees table to “users,” execute the following:
Helpful hint: ALTER TABLE on its own does nothing. Start with the command and the table name, then specify the alterations to be made.

Add a Column and Change Column Properties

The syntax for adding a column to a table is:
To remove or delete a column:
Here, we will add a string column called “address” by typing:
Add a TIMESTAMP column named “date” by entering the following command:
Add an index on the column named “id” by running:
It is also possible to do multiple additions at once:
Our table should now look like this:

Modify a Column Type

The column modification syntax is:
To modify the address field to allow for larger strings:
We can also combine multiple modifications at once:
With this command, we also changed the column called “name” to “lastname” and disallowed NULL values for it.

Changing the Default Column Value

To change the default value on a column, use the SET DEFAULT syntax:
Our final table now looks like this:

Why Should You Use ALTER TABLE?


Making changes to a MySQL database structure via the command line and ALTER TABLE can sometimes be faster and more flexible than using PHPMyAdmin or similar GUI tools.

0 comments:

Post a Comment