Alter table add column :

In my previous article I have explained about different examples of create statement. This article will help the developers to alter the table and add columns in oracle sql. Most of developers needs to add the different columns for different tables as per business requirements. Alter table statement is used to change the structure of the table. Alter table add column statement is used to modify the structure of the table and used to add the column to the table. Alter table statement is used for different purposes to add column, to increase column width, to drop column and so on.

Alter table add column without Constraints:

There are two types of columns in sql. Column with constraints and column without constraints. When there is a requirement, where user needs to add the column without constraints following syntax is helpful:

Syntax:

Alter table Table_name

Add Column_name (datatype size);

Real life Example:

Consider reporting environment and one report needs to add one extra column in product table. If Product table have product type but as per requirement product table needs to add product subtype also.

Query:

Alter table T_Product

Add Product_Sub_Type Varchar2 (30);

Note : Same Syntax is used to add the columns in oracle, MySQL, Microsoft sql server and PostgreSQL.

Alter table add column with Constraints:

There are some requirements where user needs to add the columns with constraints like IFNULL or Check constraint. So to add the columns with constraints following syntax is used:

Syntax:

Alter table Table_name

Add Column_name (datatype size) Column_Constraint;

Real life Example:

Consider user wants to add the column in product table, which have ‘not null’ constraint.

 Query:

Alter table T_Product

Add Product_Sub_Type Varchar2 (30) not null;

Note : Same Syntax is used to add the columns in oracle, MySQL, Microsoft sql server and PostgreSQL.

Alter table add column for adding multiple columns:

Sometimes business requirement needs to add multiple columns in table. Using only one alter statement user can add multiple columns in the table. The syntax is quite different :

Syntax :

Alter table Table_name

Add (Column_name1 (datatype size) Column_Constraint1,

Column_name2 (datatype size) Column_Constraint2,

………….

Column_name’n’ (datatype size) Column_Constraint’n’);

Real Life Example:

If there is a requirement to add 2 columns in customer table. First column is Gender, which has not null constraints and second is fullname column. User can achieve this using only one Alter statement.

Query:

Alter table Customer

Add

(Gender varchar2 (1) not null,

Fullname varchar2 (60));

Alter table Modify column for modifying the size of column:

Many times user facing issue of size of the variable. So to modify the column Alter table modify statement is used. User can add the constraint also using the modify statement. For any kind of modification Alter table modify statement is used.

Syntax:

Alter table Table_name

Modify (Column_name1 (datatype size) Column_Constraint1,

Column_name2 (datatype size) Column_Constraint2,

………….

Column_name’n’ (datatype size) Column_Constraint’n’);

Real Life example:

There should be different scenarios user always face and tackle using modify statement. If user needs to add the constraint to already existing column as well as user needs to modify the size of already existing column then modify statement works. Let say, in customer table if user want to add not null constraint to Gender column and needs to increase size of Fullname column from 60 to 90 then only one alter statement will work.

Query:

Alter table Customer

Modify

(Gender varchar2 (1) not null,

Fullname varchar2 (60));

Alter table drop column:

Sometimes there is requirement where user needs to drop the specific column. Also to drop the specific column user needs to use Alter table drop statement.

Syntax(For single column) :

Alter table Table_name

drop Column_name (datatype size);

Syntax (For multiple columns) :

Alter table Table_name

drop(Column_name1 (datatype size) Column_Constraint1,

Column_name2 (datatype size) Column_Constraint2,

………….

Column_name’n’ (datatype size) Column_Constraint’n’);

Real life example:

If user does not need the Gender and fullname column from customer table then user needs to use Alter table drop statement.

Query :

Alter table Customer

Drop

(Gender varchar2 (1) not null,

Fullname varchar2 (60));

Alter table rename column:

User can rename the column using alter table rename statement. There are some scenarios where user needs to change column name .User can change the name of the column using alter table rename statement.

Syntax:

Alter table tablename

Rename old_column to new_column;

User can rename the table name also. Syntax for renaming table is following:

Syntax:

Alter table tablename

Rename to new_tablename;

Real Life example:

If user wants to change the name of table from Customer to Customer1 and also wants to change the name of the column from Gender to Sex.

Rename Table:

Alter table Customer

To Customer1;

Rename Column:

Alter table Customer1

Rename Gender to Sex;

In above article I have tried to explain about all alter statements with real examples. Hope this is helpful to end users. Kindly comment in comment section if you like this article or if you have any questions on this article.