ALTER - Modify Existing Database Objects in SQL

The ALTER statement in SQL is used to modify the structure of an existing database object, such as a table, index, or column. It is part of the Data Definition Language (DDL) and allows for changes to be made without needing to recreate the object.

1. Altering a Table

The ALTER TABLE statement is used to modify the structure of an existing table. You can add, modify, or delete columns, and even rename the table.

1.1 Adding a Column

To add a new column to an existing table, you use the ADD COLUMN clause.

Example:

      -- Add a new column 'Email' to the 'Students' table
      ALTER TABLE Students
      ADD Email VARCHAR(100);
      
      -- Insert data into the new column
      UPDATE Students
      SET Email = 'alice@example.com'
      WHERE StudentID = 1;
    

1.2 Modifying a Column

If you need to change the data type or constraints of an existing column, you use the MODIFY COLUMN clause.

Example:

      -- Modify the 'Grade' column to allow longer values (changing the size)
      ALTER TABLE Students
      MODIFY COLUMN Grade VARCHAR(5);
    

1.3 Dropping a Column

If you want to remove an existing column from a table, you use the DROP COLUMN clause.

Example:

      -- Drop the 'Email' column from the 'Students' table
      ALTER TABLE Students
      DROP COLUMN Email;
    

1.4 Renaming a Table

To rename an existing table, you use the RENAME TO clause. This is supported by some RDBMS like MySQL and PostgreSQL.

Example:

      -- Rename the 'Students' table to 'Pupils'
      ALTER TABLE Students
      RENAME TO Pupils;
    

2. Altering a Database

The ALTER DATABASE statement is used to modify the properties of an existing database, such as renaming it or changing its settings.

Example:

      -- Rename an existing database from 'SchoolDB' to 'UniversityDB'
      ALTER DATABASE SchoolDB
      MODIFY NAME = UniversityDB;
    

3. Altering an Index

Although indexes are typically created using the CREATE INDEX statement, certain RDBMS allow modification of existing indexes with the ALTER INDEX statement. However, in many systems, index modifications often require dropping and recreating them.

Example:

      -- Drop and recreate an index (if modification is not supported directly)
      DROP INDEX idx_name ON Students;
      CREATE INDEX idx_name ON Students (Name);
    

4. Adding Constraints

With the ALTER TABLE statement, you can also add constraints, such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK.

Example:

      -- Add a UNIQUE constraint on the 'Email' column
      ALTER TABLE Students
      ADD CONSTRAINT unique_email UNIQUE (Email);
    

5. Removing Constraints

If you need to remove a constraint from an existing table, you use the DROP CONSTRAINT clause.

Example:

      -- Remove the UNIQUE constraint on the 'Email' column
      ALTER TABLE Students
      DROP CONSTRAINT unique_email;
    

Conclusion

The ALTER statement in SQL provides a powerful tool for modifying existing database structures. It allows you to adjust your schema as your application evolves, making it essential for ongoing database maintenance and optimization.