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.
