MySQL alter

The MySQL ALTER command is used to modify the structure of an existing database table. It can be used to add, delete or modify columns, change the data type of a column, or modify the table's constraints and indexes.

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

Examples:

1. Adding a column to a table:

ALTER TABLE students
ADD email varchar(255);

This adds a new column named 'email' of data type varchar(255) to the 'students' table.

2. Deleting a column from a table:

ALTER TABLE students
DROP COLUMN email;

This deletes the 'email' column from the 'students' table.

3. Modifying a column in a table:

ALTER TABLE students
MODIFY COLUMN email varchar(320);

This changes the data type of the 'email' column in the 'students' table from varchar(255) to varchar(320).

4. Adding a constraint to a table:

ALTER TABLE students
ADD CONSTRAINT pk_student_id PRIMARY KEY (student_id);

This adds a new primary key constraint named 'pk_student_id' to the 'students' table on the 'student_id' column.

5. Deleting a constraint from a table:

ALTER TABLE students
DROP CONSTRAINT fk_course_id;

This deletes the foreign key constraint named 'fk_course_id' from the 'students' table.