Key Points on DDL Alter Command in SQL

Overview of SQL Sublanguages

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)

Focus on Data Definition Language (DDL)

• Key DDL Commands: CREATE, ALTER, TRUNCATE, DROP, RENAME
• Previously covered CREATE command extensively

Alter Command Applications

• Modify the structure of existing tables
• Situations requiring ALTER:
  • Adding a new column
  • Modifying an existing column's data type
  • Dropping an existing column
  • Adding or dropping a column constraint

Syntax and Examples

Adding a New Column

• Syntax:

  ALTER TABLE table_name ADD column_name data_type;
  

• Example:

  ALTER TABLE student ADD age INTEGER;
  

  • Adds a new column age to the student table with INTEGER data type.

Modifying an Existing Column

• Syntax for Column Modification:
  • General:

    ALTER TABLE table_name ALTER COLUMN column_name data_type;
    

  • Alternative:

    ALTER TABLE table_name MODIFY COLUMN column_name data_type;
    

• Example:

  ALTER TABLE student ALTER COLUMN name VARCHAR(50);
  

Dropping an Existing Column

• Syntax:

  ALTER TABLE table_name DROP COLUMN column_name;
  

• Example:

  ALTER TABLE student DROP COLUMN name;
  

  • Removes the column name from the student table.

Adding a New Constraint

• Syntax:

  ALTER TABLE table_name ADD CONSTRAINT constraint_name column_constraint(column_names);
  

• Example:

  ALTER TABLE student ADD CONSTRAINT PK_ID PRIMARY KEY (ID);
  

  • Assigns a primary key constraint to the ID column in the student table.

Dropping an Existing Constraint

• Syntax:

  ALTER TABLE table_name DROP CONSTRAINT constraint_name;
  

• Example:

  ALTER TABLE student DROP CONSTRAINT PK_ID;
  

  • Removes the primary key constraint PK_ID from the ID column in the student table.

Considerations

• DDL commands are auto-committed and cannot be rolled back.
• Ensure thorough testing and validation when altering tables with existing data or constraints.