SQL ALTER TABLE Statement

Today I am going to talk about how to ALTER table in DBMS (Database Management System). It doesn't matter what is  your DBMS I'll do some SQL queries which will match to your requirement.

Simply, statement can be used to do alterations to an existing database table. We can identify those alterations as below;

• Add new column.
• Modify existing column. (Column data type)
• Drop existing column.
• Add Constraints.
• Drop Constraints.

For all demonstrations below please assume that there are two tables, Employee(EmpID, EmpName) and Department(DepID, DepName). Every employee is belong to one of department.

Add New Column

Sometimes we may need to add columns to existing data tables. To do this we have to alter the existing data table. Using SQL queries we can do it in a simple way.

Using above query you can add a column named "DepID" to the table "Employee", but for existing records value of "DepID" will be NULL. 

If you want to set a default value for existing records you can use the query below

Modify Existing Column

You can change data type of an existing column as below;

Drop Existing Column

To delete (drop) column in a table you can user following SQL queries as your DBMS.

Add Constraints

You also can add constraints (Primary Key, Foreign Key, Indexes...)using this statement. Here I am adding foreign key constraint to Employee table linking Department table.

Drop Existing Constraints

This is how you can delete (drop) existing constraints.