SQL | ALTER (ADD, DROP, MODIFY)

The ALTER statement in SQL is used to modify the structure of an existing table. With this statement, you can perform various operations, including adding new columns, modifying existing columns, or deleting columns. The specific syntax might slightly vary based on the database system you're using, but the general concepts remain consistent.

1. ADD a Column:

You can use the ADD clause with the ALTER statement to add new columns to a table.

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

Example: To add a new column named email of type VARCHAR(100) to an employees table:

ALTER TABLE employees
ADD email VARCHAR(100);

2. DROP a Column:

The DROP clause allows you to remove a column from a table.

Syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

Example: To remove the email column from the employees table:

ALTER TABLE employees
DROP COLUMN email;

Note: Dropping a column will permanently remove that column and all the data stored in it.

3. MODIFY a Column:

The MODIFY clause (or ALTER COLUMN in some databases) is used to change the datatype or size of an existing column.

Syntax (for databases like Oracle):

ALTER TABLE table_name
MODIFY column_name new_datatype;

Syntax (for databases like SQL Server):

ALTER TABLE table_name
ALTER COLUMN column_name new_datatype;

Example: To modify the data type of the email column in the employees table to VARCHAR(200):

Oracle:

ALTER TABLE employees
MODIFY email VARCHAR(200);

SQL Server:

ALTER TABLE employees
ALTER COLUMN email VARCHAR(200);

Points to Note:

Always backup your data before making structural changes to your tables.
Adding a column with a NOT NULL constraint on a table with existing rows can be problematic unless you provide a default value, as the existing rows won't have any data for this new column.
Some operations, especially dropping columns or changing data types, can be time-consuming on large tables and can lock the table, preventing other operations.

Lastly, since SQL syntax can vary across different database systems, it's a good practice to consult the documentation for your specific database when using the ALTER statement.

Dropping columns with ALTER TABLE in SQL:
- Use ALTER TABLE to drop columns.
```
ALTER TABLE your_table
DROP COLUMN column_to_drop;
```
Modifying column datatype using ALTER TABLE in SQL:
- Modify the datatype of a column.
```
ALTER TABLE your_table
MODIFY column_name new_datatype;
```

Adding constraints with ALTER TABLE in SQL:

Add constraints using ALTER TABLE.

ALTER TABLE your_table
ADD CONSTRAINT constraint_name
CHECK (column_name > 0);

How to add a primary key using ALTER TABLE in SQL:
- Add a primary key constraint.
```
ALTER TABLE your_table
ADD PRIMARY KEY (column_name);
```
Dropping indexes with ALTER TABLE in SQL:
- Remove indexes using ALTER TABLE.
```
ALTER TABLE your_table
DROP INDEX index_name;
```

Changing column properties with ALTER TABLE in SQL:

Modify column properties.

ALTER TABLE your_table
ALTER COLUMN column_name SET DEFAULT default_value;

SQL ALTER TABLE MODIFY column example:
- Example of modifying a column.
```
ALTER TABLE your_table
MODIFY column_name new_datatype;
```

Renaming columns using ALTER TABLE in SQL:

Rename a column.

ALTER TABLE your_table
RENAME COLUMN old_column_name TO new_column_name;