Changing a Table's Structure — ALTER TABLE

After a table has been created, its structure — the columns it has and their data types — can still be changed with the DDL command ALTER TABLE. This is different from UPDATE, which changes the data inside rows; ALTER TABLE changes the design of the table itself. Its main uses are:

1. Adding a new column — ADD:

ALTER TABLE Employee
ADD Email VARCHAR(60);

This gives every existing row an Email column (initially NULL) and lets new rows store an e-mail address.

2. Changing an existing column's data type — MODIFY: For example, to widen the Name column from 50 to 80 characters:

ALTER TABLE Employee
MODIFY Name VARCHAR(80);

3. Renaming a column — CHANGE: this changes both the name and the data type, so the data type must be repeated. To rename Email to EmailId:

ALTER TABLE Employee
CHANGE Email EmailId VARCHAR(60);

4. Removing a column — DROP COLUMN:

ALTER TABLE Employee
DROP COLUMN EmailId;

5. Adding a constraint — for example making Name compulsory or adding a primary key:

ALTER TABLE Employee
ADD PRIMARY KEY (EmpNo);
``` …