SQL as a Data Definition Language : ALTER

The ALTER TABLE statement to alter the structure of a table. With ALTER TABLE command we can add, rename, modify or delete columns in an existing table. The basic syntax of the ALTER TABLE statement is as follows −
 ALTER TABLE table_name ADD (column_name1 datatype, column_name2 datatype, column_nameN);

Adding A Column in Existing Table :

The basic syntax of an ALTER TABLE command to add a New Column in an existing table is as follows :
 ALTER TABLE table_name ADD (column_name datatype);
Example :
 ALTER TABLE Employee ADD (Addr varchar(20));
The above statement add a column Addrof type varchar in Employee table.


Adding Multiple Columns in Existing Table :

To add multiple columns in an existing table, the syntax would be :
 ALTER TABLE table_name ADD (column_name1 datatype, column_name2 datatype, column_nameN);
Example :
 ALTER TABLE Employee ADD (City varchar(20), Pin varchar(20), Ph varchar(20));
The above statement add three columns namely City, Pin, Ph in Employee table.


Modify an Existing Column :

The basic syntax of an ALTER TABLE command to Modify the data type of an existing column is as follows :
 ALTER TABLE table_name MODIFY (column_name datatype);
For example : Suppose we want to increase the length of the column Addr from varchar(20) to varchar(30) in a table named Employee, then the statement would be :
 ALTER TABLE Employee MODIFY (Addr varchar(30));
The above statement will modify Addr column of Employee table. We can also change the data type of a column, for example :
 ALTER TABLE Employee MODIFY (Ph NUMBER(10));
The above statement will change the data type of column Ph from VARCHAR(20) to NUMBER(10), but remember that column must be empty, in order to change its data type.


Rename a Column :

By using ALTER command, we can also rename an existing column. The basic syntax of an ALTER TABLE command to rename a column is as follows.
 ALTER TABLE table_name RENAME COLUMN old_column_name to new_column_name;
Example :
 ALTER TABLE Employee RENAME COLUMN Ph to PhoneNo;
The above statement will rename the column Ph to PhoneNo of table Employee.


Drop (Delete) A Column :

ALTER command is also used to drop columns from an existing table. The basic syntax of an ALTER TABLE command to DROP COLUMN is as follows.
 ALTER TABLE table_name DROP (column_name);
Example :
 ALTER TABLE Employee DROP (City);
The above statement will drop the column City of table Employee. Remember that you cannot drop the column if the table is having only one column, and also if the column you want to drop is having primary key constraint on it then you have to give cascade constraint clause. For example :
 ALTER TABLE Employee DROP (EmpId) cascade constraint;


Next Topic :

No comments:

Post a Comment