Alter Table in MariaDB

MariaDB is an open-source RDBMS, that offers an extensive collection of features for handling database structures effectively. One important part of database management is changing tables to meet needs or improve performance. The ALTER TABLE command in MariaDB assists in these changes, allowing users to modify, add, or remove columns, indexes, and constraints. In this article, we will go into the details of ALTER TABLE in MariaDB using various examples.

ALTER TABLE in MariaDB

The ALTER TABLE statement modifies the structure of an existing table. It enables us to insert, update, and also delete columns, alter the datatype of columns, create or delete indexes, and perform many other table operations.

With the help of ALTER TABLE users can easily modify the existing table according to their requirements.

Syntax of ALTER TABLE in MariaDB:

ALTER TABLE table_name [alter_specification [...]];

Explanation: In the above statement, table_name is the name of the table we want to modify, and alter_specification denotes the changes we want to make.

Different Ways to Use ALTER TABLE Command

Let’s first create a table and then we will do modifications in that using ALTER command.

CREATE TABLE Books 
(
bookID INT
);

Output:

Book Table

Explanation: As we can see our table was created successfully.

1. Adding a Column Using Alter Table

One of the common demands is to add a new column to an already available table.

Syntax:

ALTER TABLE table_name ADD COLUMN new_column_name data_type [column_constraints];

Explanation:

• ALTER TABLE: This clause states that we want to alter an existing table structure.
• table_name: Name of our table.
• ADD COLUMN: This clause shows that we are adding a new column to the table.
• new_column_name: Name of the new column we are adding.
• data_type [column_constraints]: Indicates the data type and any constraints such as NOT NULL, UNIQUE, or DEFAULT.

Query:

Let’s add some columns to our table Books.

ALTER TABLE Books 
ADD COLUMN bookName VARCHAR(20),
ADD COLUMN author VARCHAR(255),
ADD COLUMN price DECIMAL(10,2);

Output:

Columns

Explanation: As we can see all our columns with their data types. This will add three more columns in Books table, bookName, author and price.

2. Modifying a Column Using Alter Table

Sometimes We may need to modify the data type or constraints of an existing column.

Syntax:

ALTER TABLE table_name MODIFY COLUMN column_name new_data_type [new_column_constraints];

Query:

Let’s change the datatype of price column from DECIMAL to INT datatype using MODIFY COLUMN.

ALTER TABLE Books MODIFY COLUMN price INT;

Output:

Modified Column

Explanation: As we can see in the output image the datatype of price column changes from DECIMAL to INT.

3. Dropping a Column Using Alter Table

We can remove or dropped the column from the table.

Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Query:

Let’s drop or remove the column author from the Books table using DROP COLUMN.

ALTER TABLE Books DROP COLUMN author;

Output:

Dropped column

4. Adding Indexes Using Alter Table

Indexes are crucial for optimizing query performance. We can add or drop indexes using the ALTER TABLE statement:

Syntax:

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);

Explanation:

• INDEX: This indicates the type of the index that we are creating a regular index on the specified column.
• index_name: This is the name we want to give to the index. This is optional.
• (column1,..): This is the column name on which index is being created.

Query:

ALTER TABLE Books ADD INDEX index_price (price);

Explanation: When we run this statement, the database will generate an index on the price column, which can significantly increase the speed of requests related to this column. we will not observe any direct output on our side, but we can use tools like database management consoles or command line interfaces to check the presence of the new index on the price column of the Books table.

5. Dropping Indexes Using Alter Table

The syntax for renaming a column involves using the DROP INDEX clause.

Syntax:

ALTER TABLE table_name DROP INDEX index_name;

Query:

Let’s remove the index index_price from Books Table using DROP INDEX command.

ALTER TABLE Books DROP INDEX index_price (price);

This statement tells the database engine to delete the index of index_price from Books table when we run it. Dropping an index can be necessary when we wish to change the structure of our database, improve the performance, or the index is no longer in demand

6. Rename Column in Table Using Alter Table

The syntax for renaming a column involves using the CHANGE COLUMN clause.

Syntax:

ALTER TABLE table_name CHANGE COLUMN old_name new_name data_type

Explanation:

• ALTER TABLE: This clause tells we that we want to modify an existing table structure.
• table_name: Name of our table.
• CHANGE COLUMN old_name new_name: Here, we can see that we are renaming the column name from old name to new name.
• data_type: Specify the data type.

Query:

Let’s alter or change the name of Column from bookName to title.

ALTER TABLE Books CHANGE COLUMN bookName title VARCHAR(50);

Output:

Rename Column

Explanation: This example will change the bookName column to title.

7. Renaming a Table Using Alter Table

For renaming a table, RENAME TO clause is used.

Syntax:

ALTER TABLE old_table_name RENAME TO new_table_name

Query:

Let’s alter or change the name of Book table to NewBooks Table.

ALTER TABLE Books RENAME TO NewBooks;

Output:

Renamed Table Name

Explanation: Now the table named Books is renamed to NewBooks.

Conclusion

The MariaDB ALTER TABLE command is a useful for structural modifications to the databases. Whether we have to create or change columns, add or delete indexes, or perform other manipulations, MariaDB offers a wide range of commands for such activities to do them easily. The syntax of ALTER TABLE needs to be understood clearly along with the specific requirements of the database schema to avoid unwanted effects.