Modifying Structure (DDL)

The ALTER TABLE command is used to add, modify, or remove columns and constraints from an existing table. This is one of the most common DDL operations, as database needs frequently change over time.

1. Adding a New Column (ADD COLUMN)

You often need to incorporate new fields into an existing table structure.

Syntax:

ALTER TABLE table_name
ADD COLUMN new_column_name datatype [constraints] [position];

The optional position (e.g., AFTER existing_column) dictates where the column appears. If omitted, the column is added to the end.

Execution Example: Adding a Phone Number Field

Let’s add a phone_number column to our existing Employees table.

USE HR_Management;

-- Add a new column to store contact number, ensuring it cannot be empty.
ALTER TABLE Employees
ADD COLUMN phone_number VARCHAR(15) NOT NULL; 

2. Modifying an Existing Column (MODIFY COLUMN)

This command allows you to change the data type, size, or constraints of a column.

Syntax:

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype [new_constraints];

Execution Example: Increasing Column Size

If you anticipate longer last names, you may need to increase the size of the last_name column.

-- Change the VARCHAR size from 50 to 75
ALTER TABLE Employees
MODIFY COLUMN last_name VARCHAR(75) NOT NULL;

Caution: Decreasing the size of a column (VARCHAR(100) to VARCHAR(50)) will cause data truncation (loss) if any existing records contain values longer than the new size.

3. Dropping a Column (DROP COLUMN)

To remove a column and all its associated data from a table.

Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

Execution Example: Removing the Phone Number Field

Let’s remove the phone_number column we just added.

-- Remove the phone_number column entirely
ALTER TABLE Employees
DROP COLUMN phone_number;

Warning: This action is irreversible. All data in the dropped column will be permanently lost.

4. Adding and Dropping Constraints

Constraints are also managed using ALTER TABLE. This is typically done after a table is created.

5. Dropping Tables and Databases (DROP)

These commands permanently remove database objects.

• DROP TABLE: Permanently removes a table from the database, including all its data and structure.SQLDROP TABLE Employees;
• DROP DATABASE: Permanently removes an entire database.SQLDROP DATABASE HR_Management;

Extreme Caution: The DROP command is the most destructive DDL operation and should be used with extreme care, as there is no built-in undo feature.