ALTER TABLE in SQL

ALTER TABLE is used to change the structure of an existing table.

ALTER TABLE students
ADD email VARCHAR(100);

This adds a new email column to the students table.

The basic syntax is:

ALTER TABLE table_name
operation;

The operation can be used to add, modify, rename, or remove a column.

The ADD COLUMN command adds a new column to an existing table.

ALTER TABLE students
ADD COLUMN email VARCHAR(100);

A new email column will be added.

You can add columns with numeric data types.

ALTER TABLE students
ADD COLUMN age INT;

This adds an age column that can store integer values.

You can add multiple columns in one ALTER TABLE statement.

ALTER TABLE students
ADD COLUMN phone VARCHAR(15),
ADD COLUMN address VARCHAR(200);

Both phone and address columns are added.

You can add a column for storing dates.

ALTER TABLE students
ADD COLUMN admission_date DATE;

This creates an admission_date column.

You can define a default value when adding a column.

ALTER TABLE students
ADD COLUMN status VARCHAR(20) DEFAULT 'Active';

New records can automatically use Active as the default status.

In MySQL, the MODIFY COLUMN command can change the definition of an existing column.

ALTER TABLE students
MODIFY COLUMN name VARCHAR(150);

This changes the size of the name column.

You can change the data type of a numeric column.

ALTER TABLE students
MODIFY COLUMN age BIGINT;

The age column is changed from its previous definition to BIGINT.

You can modify whether a column allows NULL values.

ALTER TABLE students
MODIFY COLUMN name VARCHAR(100) NOT NULL;

This makes the name column required.

You can modify the default value of a column.

ALTER TABLE students
ALTER COLUMN status SET DEFAULT 'Active';

This syntax is supported by some SQL database systems. MySQL commonly uses:

ALTER TABLE students
ALTER status SET DEFAULT 'Active';

In MySQL, RENAME COLUMN can be used to change a column name.

ALTER TABLE students
RENAME COLUMN phone TO mobile;

The phone column is renamed to mobile.

MySQL also supports the CHANGE COLUMN syntax.

ALTER TABLE students
CHANGE COLUMN phone mobile VARCHAR(15);

CHANGE can rename the column and define its data type at the same time.

The DROP COLUMN command removes a column from a table.

ALTER TABLE students
DROP COLUMN email;

The email column and its data are removed.

You can remove more than one column from a table.

ALTER TABLE students
DROP COLUMN email,
DROP COLUMN address;

Both columns will be removed.

You can rename an existing table using RENAME TO.

ALTER TABLE students
RENAME TO learners;

The table name changes from students to learners.

You can add a primary key to an existing table.

ALTER TABLE students
ADD PRIMARY KEY (student_id);

The student_id column becomes the primary key.

You can add a UNIQUE constraint to an existing column.

ALTER TABLE students
ADD UNIQUE (email);

This prevents duplicate email values.

You can add a foreign key to connect two tables.

ALTER TABLE students
ADD FOREIGN KEY (course_id)
REFERENCES courses(course_id);

This creates a relationship between students and courses.

You can add a CHECK constraint to restrict values.

ALTER TABLE students
ADD CHECK (age >= 18);

This requires the age value to be at least 18.

You can give a constraint a name.

ALTER TABLE students
ADD CONSTRAINT chk_age
CHECK (age >= 18);

The constraint is named chk_age.

Constraints can be removed using ALTER TABLE. The exact syntax depends on the database system and constraint type.

For example, a MySQL foreign key can be removed using:

ALTER TABLE students
DROP FOREIGN KEY fk_course;

The named foreign key constraint is removed.

In MySQL, you can modify an integer column to use AUTO_INCREMENT.

ALTER TABLE students
MODIFY student_id INT AUTO_INCREMENT;

New student IDs can then be generated automatically.

ALTER TABLE is useful when the original table structure needs to change after the table has already been created.

CREATE TABLE students (
    student_id INT,
    name VARCHAR(100)
);

ALTER TABLE students
ADD email VARCHAR(100);

The table is first created and then modified.

ALTER TABLE changes the structure of a table.

UPDATE changes existing data inside a table.

ALTER TABLE students
ADD email VARCHAR(100);

UPDATE students
SET email = 'student@example.com'
WHERE student_id = 101;

ALTER TABLE changes table structure.

DELETE removes records from a table.

ALTER TABLE students
ADD phone VARCHAR(15);

DELETE FROM students
WHERE student_id = 101;

In MySQL, you can use DESCRIBE to view the table structure.

DESCRIBE students;

You can check the columns and their definitions before modifying the table.

A common mistake is trying to add a column that already exists.

ALTER TABLE students
ADD email VARCHAR(100);

If the email column already exists, the database may return an error.

Suppose your students table needs a mobile number column.

ALTER TABLE students
ADD mobile VARCHAR(15);

Now the table can store mobile numbers for students.

Here is a practical example that adds, modifies, renames, and removes columns.

ALTER TABLE students
ADD email VARCHAR(100);

ALTER TABLE students
MODIFY email VARCHAR(150);

ALTER TABLE students
RENAME COLUMN email TO email_address;

ALTER TABLE students
DROP COLUMN email_address;

These commands demonstrate common ALTER TABLE operations.