Modify or make changes in existing database with ALTER statement

 In SQL, the ALTER statement is used to modify existing database objects such as tables, columns, constraints, or indexes. The specific syntax and options may vary slightly depending on the database management system (DBMS) you are using.

Table of content

• Alter Table
• Add a column to a table
• Rename a column in a table
• Drop a column from a table
• Add a constraint to a table
• Drop a constraint from a table
• Change / Modify Datatype
• Conclusion

Modify /make changes in SQL table with ALTER statement

Syntax

    
ALTER TABLE table_name;
    
  

Replace table_name with the actual name of the table you wish to alter.

 

Add a column to a table

To add a new column to an existing table, use the following syntax:

Syntax

    
ALTER TABLE table_name
ADD column_name data_type;
    
  

Replace table_name with the name of the table, column_name with the name of the new column you want to add, and data_type with the appropriate data type for the column.

Rename a column in a table

To modify an existing column in a table, such as changing its data type or default value, use the following syntax:

Syntax

    
ALTER TABLE table_name
RENAME COLUMN old_name to new_name; 
  

Replace table_name with the name of the table, column_name with the name of the column you want to modify, and data_type with the new data type for the column.

Drop a column from a table

To remove an existing column from a table, use the following syntax:

Syntax

    
ALTER TABLE table_name
DROP COLUMN column_name;
  

Replace table_name with the name of the table, and column_name with the name of the column you want to drop.

Add a constraint to a table

To add a constraint to a table, such as a primary key or a foreign key constraint, use the following syntax:

Syntax

    
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_details;
  

Replace table_name with the name of the table, constraint_name with a meaningful name for the constraint, and constraint_details with the specific details of the constraint (e.g., PRIMARY KEY, FOREIGN KEY, etc.).

Drop a constraint from a table

To remove an existing constraint from a table, use the following syntax:

Syntax

    
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
  

Replace table_name with the name of the table, and constraint_name with the name of the constraint you want to drop.

Change / Modify Datatype

The syntax for changing the data type of a column using the ALTER statement in SQL

Syntax

    
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;
  

Example of changing column name:

 

Let's consider a table called "employees" with the following structure: 

Now, suppose we want to rename the column "hire_date" to "employment_date". We can use the ALTER TABLE statement to make this change: 

This ALTER statement renames the column "hire_date" in the "employees" table to "employment_date". The RENAME COLUMN statement allows us to change the name of the column.

After executing the ALTER TABLE statement, the updated structure of the "employees" table will be as follows:  

Example

    
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  employment_date DATE
);
  

 

By using the ALTER TABLE statement with the RENAME COLUMN option, we have successfully changed the name of the column from "hire_date" to "employment_date" in the "employees" table.

Conclusion:

 ALTER TABLE statement in SQL is used to change an existing table. It allows you to add or remove columns, modify the properties of existing columns, add or remove constraints, and perform other modifications to the table structure. With ALTER TABLE, you can make changes to the table without deleting and recreating it, providing flexibility in managing your database structure.