ALTER TABLE Statements in SQL

ALTER TABLE Statements in SQL

Overview

ALTER TABLE statements are used in database management systems to modify the structure of an existing table. These statements allow you to add, modify, or drop columns, change data types, add constraints, and perform other table-related operations. This documentation provides a brief explanation of the syntax and examples of commonly used ALTER TABLE statements.

Syntax

The basic syntax for an ALTER TABLE statement is as follows:

sql
ALTER TABLE table_name
    action;

Where:

• ALTER TABLE is the keyword indicating the intention to modify a table.
• table_name is the name of the table you want to alter.
• action represents the specific alteration you want to perform on the table.

Common ALTER TABLE Actions

1. Adding a Column: To add a new column to an existing table, you can use the following syntax:

   sql
   ALTER TABLE table_name
   ADD column_name data_type;
   

   Example:

   sql
   ALTER TABLE customers
   ADD email VARCHAR(100);
   

2. Modifying a Column: If you need to change the data type or size of a column, you can use the MODIFY clause:

   sql
   ALTER TABLE table_name
   MODIFY column_name new_data_type;
   

   Example:

   sql
   ALTER TABLE employees
   MODIFY salary DECIMAL(10,2);
   

3. Dropping a Column: To remove a column from a table, you can use the DROP COLUMN statement:

   sql
   ALTER TABLE table_name
   DROP COLUMN column_name;
   

   Example:

   sql
   ALTER TABLE orders
   DROP COLUMN status;
   

4. Adding a Constraint: You can add various constraints to a table, such as primary key, foreign key, or unique constraints, using the following syntax:

   sql
   ALTER TABLE table_name
   ADD CONSTRAINT constraint_name constraint_type (column_name);
   

   Example:

   sql
   ALTER TABLE products
   ADD CONSTRAINT pk_products PRIMARY KEY (product_id);
   

Conclusion

ALTER TABLE statements provide a powerful way to modify the structure of existing tables in a database. By using these statements, you can add, modify, or drop columns, change data types, and add constraints, among other operations. Understanding the syntax and common actions of ALTER TABLE statements is essential for efficient database management.