How to Use MySQL ALTER TABLE command?

MySQL is the most popular open-source relational database management system (RDBMS) developed by Oracle Corporation, and it has become a staple for developers and data professionals.  It is very important to choose the right database management system for any organization to make the right decision. Many companies, such as Facebook, YouTube, Spotify, Airbnb, Uber, Netflix, LinkedIn, Booking.com, and GitHub, use  MySQL for their database management system.

When working with MySQL, one of the powerful commands to master is the ‘ALTER TABLE’ command. This allows you to modify the existing structure of the table by ADD, DELETE, setting constraints, renaming the table, or changing columns. In this blog, we will discuss the various aspects of MySQL ALTER TABLE and its syntax, with examples,  privileges, and constraints for executing ALTER TABLE commands to ensure effective database management practices.

What is MySQL?

A database is a collection of structured information and it is controlled by Database Management Systems(DBMS). MySQL is a powerful open-source relational database management system (RDBMS). It stores and manages data in the form of tables, which are organized into rows and columns. 

It uses Structured Query Language(SQL) for database management, which allows users to construct, manage, control, and query data.MySQL supports various data formats such as numeric formats, string formats, date and time formats, and JSON formats. It stores data in an organized schema, which defines how data is organized and stored and describes the relationship among various tables.

Transactions with ACID properties in MySQL ensure that databases maintain integrity and consistency, even during errors or system failure.  ACID: Atomicity, Consistency, Isolation, Durability. It is designed to handle large databases and handle a high volume of concurrent connections efficiently, making it suitable for any applications ranging from small websites to large-scale enterprise systems.

• Transactions properties:  MySQL ensures data integrity and reliability during operations, supporting ACID transactions. 
• System Compatibility:  It runs on various operating systems (Windows, Linux, Unix, and MacOS), allowing flexibility during development. 
• Security: It contains various security layers that help in protecting sensitive data. 
• Roll-back: Ensures the database can recover from failures by allowing transactions to roll back, commit, and recover.
• Replication: It supports data redundancy and high availability across multiple servers.

What is the ALTER TABLE Command in MySQL?

ALTER TABLE is essential in MySQL as it maintains data integrity and also manages database schema. ALTER TABLE statement is used to change the structure of an existing table. It can also add, delete, modify, drop columns, change the data type of a particular column, create indexes, or even add another primary key to a table. However, ALTER TABLE offers significant flexibility, so it should be used cautiously. For example, while using a DROP table, it may result in data loss if not handled carefully. Overall, ALTER TABLE is a powerful tool for developers,  as it plays a critical role in maintaining and optimizing database systems.

Now, let’s have some practical examples of how to use the ALTER TABLE command in SQL: 

Before going into ALTER TABLE, we will create an employee  table  in MySQL, which contains some columns that might be useful for the employee management system:

To create a table in SQL, there are three major things to be done:

• Table name
• Define columns
• Define column data types

Create table syntax:

CREATE TABLE table_name (

    column_name1 data_type constraints,

    column_name2 data_type constraints,

    ...

    column_nameN data_type constraints

);

The initial setup – Creating the Employee Table

1. Create the employee table.

CREATE TABLE employee (

   employee_id INT AUTO_INCREMENT PRIMARY KEY,

    first_name VARCHAR(50) NOT NULL,

    last_name VARCHAR(50) NOT NULL,

    birthdate DATE,

    job_title VARCHAR(50),

    department_id INT );

2. Insert value to the employee table.

INSERT INTO employee (first_name, last_name, birthdate, job_title, department_id) VALUES ('John', 'Doe', '1985-06-15', 'Software Engineer', 1), 

('Jane', 'Smith', '1990-08-20', 'Project Manager', 2), 

('Alice', 'Johnson', '1992-02-10', 'Data Analyst', 1),

('Bob', 'Brown', '1988-11-05', 'UX Designer', 3);

Output

3. DESC table structure

After inserting values into the table, using DESC, we can check the structure of the table

DESC employee;

At this point, the employee table is complete and ready for further action. 

Where Can You Use ALTER TABLE Command?

After creating an employee table in SQL, we can modify the table’s structure using the ALTER TABLE command. Here’s a breakdown of the scenario, where ALTER TABLE can be used, along with examples: 

1. ADD A NEW COLUMN 

If there is a need to store additional information, such as an employee’s phone number, we can use the ALTER command to add a new column instead of creating a new table. Using ALTER ADD, multiple columns can also be added.

Syntax

ALTER TABLE table_name

ADD column_name data_type [constraints];

Query

ALTER TABLE employee ADD COLUMN phone_number VARCHAR(15);

2. MODIFY AN EXISTING COLUMN

Using modify, we can change the data type of an existing column. In this example we use MODIFY to change the length of the first_name column from VARCHAR(50) to VARCHAR(100).

Syntax

ALTER TABLE table_name

MODIFY COLUMN column_name data_type [constraints];

Query

ALTER TABLE employee MODIFY COLUMN first_name VARCHAR(100) NOT NULL;

3. DROP A COLUMN 

For instance, if a column is no longer needed, it can be removed using DROP. 

Syntax 

ALTER TABLE table_name

DROP COLUMN column_name;

Query

ALTER TABLE employee DROP COLUMN birthdate;

Disadvantages of dropping a column:

• Data Loss – Permanently removes all data in that column.
• Affect Dependent Objects.
• Rollback Complication.

4. RENAME A COLUMN

Using the following statement below, you can rename a column. Note: Depending on the SQL database the syntax may vary (e.g., MySQL, PostgreSQL, SQL Server).

Syntax

ALTER TABLE table_name

RENAME COLUMN old_column_name TO new_column_name;

Query

ALTER TABLE employee

RENAME COLUMN job_title TO employee_position;

5. ADDING A CONSTRAINT

ADD CONSTRAINT is used to add constraints such as a primary key or foreign key to an already existing table. 

Primary key – This is a unique identifier for each row in a table.

Foreign key – Link data in one table to another if the value only matches.

Syntax for adding primary key:

ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY (col1, col2);

Query

ALTER TABLE employee ADD CONSTRAINT  ph_unique UNIQUE(phone_number);

6. To drop a constraint:

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

Overall, ALTER TABLE command is a powerful tool for effective data management. However, it is always important to back up data before making any significant structural changes to maintain data.

What Are the Different Aspects of the ALTER TABLE Statement?

The ALTER TABLE command allows you to modify the table structure. Several other aspects are related to using ALTER TABLE, especially MySQL. Below are some of them with an example.

1. TABLE OPTION

The table option in the ALTER TABLE allows you to modify the table characteristic of a table in SQL.  

The syntax below is used to change the storage engine of a table in MySQL, along with the ALTER TABLE using the ENGINE option. 

Syntax

ALTER TABLE table_name ENGINE = new_storage_engine;

Query

This command changes employees’ use of the InnoDB storage engine.

ALTER TABLE employees ENGINE = InnoDB;

2. ENABLE OR DISABLE KEY

For performance reasons,  in ALTER TABLE, there is a command to enable or disable the key.

Disabling Key Syntax

ALTER TABLE table_name DISABLE KEYS;

Query for Disabling key

ALTER TABLE employees DISABLE KEYS;

Enabling Key Syntax

ALTER TABLE table_name ENABLE KEYS;

Query for Enable key

ALTER TABLE employees ENABLE KEYS;

3. CREATE OR DROP INDEXES 

To improve query performance, ALTER TABLE INDEX can be used.

ALTER TABLE table_name ADD INDEX index_name (column_name);

Query

ALTER TABLE employees ADD INDEX idx_last_name (last_name);

Managing space requirements, query performance, table option in MySQL involves several strategies, including storage engine, adjusting row increment values, changing row formats, disabling or enabling keys,  dropping unused columns, and optimizing tables.

Note:  Firstly, consider testing in a development environment before deploying into production level. 

What are the privileges and constraints required for the ALTER TABLE command?

ALTER TABLE requires various privileges and constraints, defining how and when you can modify a table.

PRIVILEGES 

It defines the specific permissions granted to users or employees with specific roles that dictate what specific actions they are allowed to perform on database objects. 

1. ALTER Privilege

Without this privilege, a user cannot execute ALTER TABLE commands. This is the primary permission to modify the structure of the table.

 Syntax

GRANT ALTER ON database_name.table_name TO 'username'@'host';

Query

GRANT ALTER ON company.employees TO 'alice'@'localhost';

To revoke the privilege:

REVOKE ALTER ON company.employees FROM 'john'@'localhost';

2. INDEX Privilege

Required for indexing on the table, either add or drop.

Syntax

GRANT INDEX ON database_name.table_name TO 'username'@'host';

Query

GRANT INDEX ON inventory.products TO 'bob'@'localhost';

Inventory.products –  Privilege for ‘bob’ being granted specifically for the ‘products’ table in the ‘inventory’ database.

Using this privilege bob can alter the table using the ALTER TABLE command. 

ALTER TABLE inventory.products ADD INDEX idx_product_name(product_name);

To revoke privilege:

REVOKE INDEX ON inventory.products FROM 'bob'@'localhost';

3. REFERENCE Privilege

This privilege is necessary when modifying foreign key constraints, as it ensures that the user has permission on the referenced table or not.

These privileges help database security and control over the schema, ensuring that only authorized users can make significant changes.

Query

GRANT REFERENCES ON company.departments TO 'alice'@'localhost';

company – database with two tables, employees and departments. Allowing Alice to create foreign key constraints that refer to the department table.

With this constraint, Alice can now create references using reference privilege.

ALTER TABLE employees 

ADD CONSTRAINT fk_department 

FOREIGN KEY (department_id) REFERENCES departments(id);

To revoke the privilege:

REVOKE REFERENCES ON company.departments FROM 'alice'@'localhost';

CONSTRAINTS

Constraints refer to rules or limitations that apply to the table or table’s structure.

1. Table Locks:  ALTER TABLE may apply table lock preventing concurrent transactions. During the alteration process, it can impact the accessibility and performance of the table.
2. Data Integrity Constraints: These are the predefined rules, such as setting primary key or foreign key constraints.
3. Transaction Complication: ALTER TABLE operations are not transactional in some databases, which means that once executed, we cannot perform rollback operations.

Best Practices for Using MySQL ALTER TABLE Command

While using the ALTER TABLE command in MySQL, it requires intensive planning and execution. Some best practices for using MySQL ALTER TABLE command include:

• Before deploying the code in the production database, test the code in the staging area or development environment. This involves catching the issues early.
• When altering columns,  choosing the appropriate data type is important. Mismatched data types can lead to poor performance.
• To optimize performance, keeping the indexes updated is important. 
• Backing up data before making structural changes and documenting the changes helps in tracking if any issue arises.

Conclusion

In MySQL, using the ALTER TABLE command is one of the powerful tools for managing the structure of the table. It allows a wide range of operations, including adding a column to an already existing table, dropping a column, managing indexes, and granting privileges and constraints. To ensure successful modification, it is important to follow some of the ALTER TABLE best practices, such as documenting the database structural changes, backing up the original data before making any changes, and also monitoring performance. By using ALTER TABLE, developers or database administrators ensure database reliability and performance optimization.

You can also transform your data seamlessly using Hevo’s in-built transformation features. Sign up for the 14-day free trial and experience seamless ETL processes.

Frequently Asked Questions