MySQL ALTER TABLE

ALTER TABLE changes the structure of a table that already exists, without losing its data.

Add a column

ALTER TABLE students ADD COLUMN email VARCHAR(100);

Existing rows get NULL in the new column. You can choose the place with AFTER name or FIRST.

ALTER TABLE students ADD COLUMN phone VARCHAR(15) AFTER name;

---

Change the data type of a column

ALTER TABLE students MODIFY COLUMN email VARCHAR(150) NOT NULL DEFAULT '';

---

Rename a column

ALTER TABLE students RENAME COLUMN phone TO mobile;

---

Drop a column

ALTER TABLE students DROP COLUMN email;
ALTER TABLE students DROP COLUMN mobile;

---

Rename the table

RENAME TABLE students TO pupils;
RENAME TABLE pupils TO students;

---

Add and remove constraints

ALTER TABLE students ADD UNIQUE (name);        -- if names must be unique
ALTER TABLE students ADD FOREIGN KEY (course_id) REFERENCES courses(id);

---

Summary

• ADD COLUMN, MODIFY COLUMN, RENAME COLUMN and DROP COLUMN change the columns.
• RENAME TABLE renames the table.
• Next: MySQL Aliases.

• Previous Page
• Next Page

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.