WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

MySQL Alter Table

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