• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL Alter Table

← Previous Next →

The MySQL ALTER TABLE statement is used to modify the structure of an existing table. Unlike CREATE TABLE, which defines a table from scratch, ALTER TABLE allows you to:

  • Add, modify, or delete columns.

  • Add or remove constraints such as primary keys or foreign keys.

  • Rename tables or columns.

This statement is essential for maintaining and updating databases as application requirements evolve. Proper use of ALTER TABLE ensures that database changes are safe, efficient, and consistent.

What Is ALTER TABLE?

In MySQL, ALTER TABLE is a DDL (Data Definition Language) command that changes the structure of an existing table without losing the data stored inside it.

Key Points:

  • Enables adding, dropping, or modifying columns.

  • Allows adding or dropping constraints like primary keys, unique keys, and foreign keys.

  • Can rename tables and columns.

  • Useful for adapting to new requirements or correcting design mistakes.

ALTER TABLE Syntax

The basic syntax varies depending on the operation:

Add a column:

ALTER TABLE table_name
ADD column_name datatype [constraints];

Modify a column:

ALTER TABLE table_name
MODIFY column_name new_datatype [constraints];

Drop a column:

ALTER TABLE table_name
DROP COLUMN column_name;

Rename a column:

ALTER TABLE table_name
CHANGE old_column_name new_column_name datatype;

Rename a table:

ALTER TABLE old_table_name
RENAME TO new_table_name;

Explanation:

  • table_name: Name of the table to modify.

  • column_name: Name of the column to add, modify, or drop.

  • [constraints]: Optional rules like NOT NULL, DEFAULT, or AUTO_INCREMENT.

Adding Columns

To add a new column to an existing table:

ALTER TABLE students
ADD email VARCHAR(100);

Explanation:

  • Adds a new column email to the students table.

  • Existing data remains intact.

  • You can also add multiple columns at once:

ALTER TABLE students
ADD phone VARCHAR(20),
ADD address VARCHAR(200);

Modifying Columns

To change the datatype or constraints of a column:

ALTER TABLE students
MODIFY age INT NOT NULL;

Explanation:

  • Changes the age column to INT and disallows NULL values.

  • Use MODIFY when keeping the column name the same but changing its properties.

To rename and change a column:

ALTER TABLE students
CHANGE student_name full_name VARCHAR(100) NOT NULL;

Explanation:

  • student_name is renamed to full_name.

  • Its datatype and constraints are updated in a single command.

Dropping Columns

To remove a column that is no longer needed:

ALTER TABLE students
DROP COLUMN address;

Explanation:

  • Deletes the address column permanently.

  • Data in that column will be lost.

  • Always backup important data before dropping columns.

Renaming a Table

To rename a table:

ALTER TABLE students
RENAME TO school_students;

Explanation:

  • Changes the table name while preserving all data and structure.

  • Useful for maintaining clear naming conventions as the project evolves.

Adding Constraints

You can also use ALTER TABLE to add constraints like primary keys or foreign keys:

ALTER TABLE enrollments
ADD CONSTRAINT pk_enrollment PRIMARY KEY (enrollment_id);

Foreign key example:

ALTER TABLE enrollments
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES students(student_id);

Explanation:

  • Ensures data integrity by linking tables logically.

  • Prevents invalid data entry.

Dropping Constraints

To remove a constraint:

ALTER TABLE enrollments
DROP FOREIGN KEY fk_student;

Explanation:

  • Removes the foreign key constraint.

  • Allows changes in table relationships without deleting the table itself.

Practical Use Cases

  1. Adding New Features: Add new columns to support additional data requirements.

  2. Correcting Design Mistakes: Modify column types or constraints after initial design.

  3. Renaming Tables/Columns: Improve clarity and maintain consistent naming conventions.

  4. Updating Relationships: Add or remove foreign keys as project requirements evolve.

  5. Removing Obsolete Columns: Drop columns no longer needed to simplify the table structure.

Best Practices for ALTER TABLE

  1. Backup Before Altering: Always backup your table before making changes.

  2. Test in Development Environment: Apply changes in a staging or test environment first.

  3. Use Descriptive Names: Maintain clear, meaningful names for columns and tables.

  4. Minimize Disruption: Apply changes during maintenance windows to avoid affecting active users.

  5. Check Dependencies: Ensure that foreign keys, triggers, or application queries won’t break.

Common Mistakes to Avoid

  1. Altering Production Tables Without Backup: Can cause irreversible data loss.

  2. Dropping Columns Without Verification: All data in the column will be lost.

  3. Modifying Datatypes Incorrectly: May cause truncation or invalid data.

  4. Ignoring Constraints: Removing or adding constraints improperly can compromise data integrity.

  5. Not Testing Changes: Always test on development or staging before production deployment.

Summary of the Tutorial

The MySQL ALTER TABLE statement is a versatile tool for modifying table structures without losing existing data.

  • Use ADD to add new columns.

  • Use MODIFY or CHANGE to modify columns.

  • Use DROP COLUMN to remove unwanted columns.

  • Rename tables or columns as needed.

  • Add or drop constraints to maintain data integrity.

Mastering ALTER TABLE ensures that your database structure can evolve safely and efficiently with changing application requirements.

Practice Questions

  1. Write a query to add a column email (VARCHAR 100) to the students table.

  2. Modify the age column in the students table to make it NOT NULL.

  3. Rename the column student_name to full_name in the students table and change its datatype to VARCHAR(100).

  4. Drop the column address from the students table.

  5. Rename the table students to school_students.

  6. Add a primary key constraint pk_enrollment on the enrollment_id column of the enrollments table.

  7. Add a foreign key fk_student on student_id in the enrollments table referencing students(student_id).

  8. Drop the foreign key fk_student from the enrollments table.

  9. Add two new columns phone (VARCHAR 20) and city (VARCHAR 50) to the students table in a single ALTER TABLE statement.

  10. Modify the credits column in the courses table to have a default value of 3.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top