SQL workshop: ALTER statement

Photo by Sigmund on Unsplash

SQL workshop: ALTER statement

The ALTER statement in SQL is used to modify the structure of an existing table. This includes operations such as adding, deleting, or modifying columns, changing the datatype of a column, adding or dropping constraints, and more. The ALTER statement essentially allows you to change the architecture of your tables without having to delete and recreate them.

Common Usages:

  1. Adding a Column:

     ALTER TABLE table_name
     ADD column_name datatype;
    
  2. Dropping a Column:

     ALTER TABLE table_name
     DROP COLUMN column_name;
    
  3. Modifying a Column:

     ALTER TABLE table_name
     MODIFY column_name new_datatype;
    
  4. Adding a Constraint:

     ALTER TABLE table_name
     ADD CONSTRAINT constraint_name constraint_type (column_name);
    
  5. Dropping a Constraint:

     ALTER TABLE table_name
     DROP CONSTRAINT constraint_name;
    

Workshop Exercise:

Objective: Practice using the ALTER statement to modify the structure of a table.

  1. Setup:

    • Create a table named 'students' with the following columns:

      • id (integer, primary key)

      • first_name (varchar)

      • last_name (varchar)

    CREATE TABLE students (
        id INT PRIMARY KEY,
        first_name VARCHAR(50),
        last_name VARCHAR(50)
    );
  1. Task 1: Add a new column to the 'students' table named 'date_of_birth' with a datatype of DATE.

     ALTER TABLE students
     ADD date_of_birth DATE;
    

  2. Task 2: Modify the 'first_name' column in the 'students' table to increase its length to 100.

     ALTER TABLE students
     MODIFY first_name VARCHAR(100);
    

  3. Task 3: Add a UNIQUE constraint to the 'id' column in the 'students' table (Note: This might seem redundant since id is already a primary key, but it's just for practice purposes).

     ALTER TABLE students
     ADD CONSTRAINT unique_id UNIQUE(id);
    

  4. Task 4: Drop the UNIQUE constraint you just added in Task 3.

     ALTER TABLE students
     DROP CONSTRAINT unique_id;
    

  5. Task 5: Drop the 'date_of_birth' column from the 'students' table.

     ALTER TABLE students
     DROP COLUMN date_of_birth;
    

After completing these tasks, the participants will have a deeper understanding of how to use the ALTER statement to modify the structure of existing tables in SQL.

References

  1. SQL ALTER TABLE Statement