WS WhereIsStuff
Tutorial Guides
Log In
Sign Up

Advertisement

Google Ad Slot: content-top

MySQL Alter Table

~1 min read · MYSQL
On this page
❮ Previous Next ❯

MySQL ALTER TABLE Statement


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 ❯