MySQL ALTER TABLE: Modify Table Structure with Ease

The ALTER TABLE statement in MySQL is used to modify an existing table by adding, deleting, renaming, or changing columns, constraints, and indexes.

1. Adding a Column

To add a new column to an existing table:

Example

ALTER TABLE students ADD COLUMN address VARCHAR(255);

Adds an address column with a VARCHAR(255) data type.

2. Deleting a Column

To remove a column from a table:

Example

ALTER TABLE students DROP COLUMN address;

Removes the address column.

3. Renaming a Column

To rename a column:

Example

ALTER TABLE students RENAME COLUMN name TO full_name;

Changes name to full_name.

4. Changing Column Data Type

To modify the data type of a column:

Example

ALTER TABLE students MODIFY COLUMN age SMALLINT;

Changes age column to SMALLINT.

5. Renaming a Table

To rename a table:

Example

ALTER TABLE students RENAME TO learners;

Renames students table to learners.

6. Adding a Primary Key

Example

ALTER TABLE students ADD PRIMARY KEY (student_id);

Sets student_id as the primary key.

7. Removing a Primary Key

Example

ALTER TABLE students DROP PRIMARY KEY;

Removes the primary key

Warning

Ensure the column is not AUTO_INCREMENT before dropping

8. Adding a Foreign Key

Example

ALTER TABLE enrollments

ADD CONSTRAINT fk_student FOREIGN KEY (student_id)

REFERENCES students(student_id) ON DELETE CASCADE;

Links student_id in enrollments to students.

9. Removing a Foreign Key

To drop a foreign key constraint, find its name first:

Example

SHOW CREATE TABLE enrollments;

Then, drop the foreign key:

Example

ALTER TABLE enrollments DROP FOREIGN KEY fk_student;

10. Adding an Index

Example

ALTER TABLE students ADD INDEX idx_name (name);

Creates an index on name for faster searches.

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.