SQL provides the statement ALTER TABLE that allows you to change the structure of a table. We would like to add the column color of the datatype varchar to the table called jeans. 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.You want to add a new column to an existing table. User can change the name of the column using alter table rename statement. There are some scenarios where user needs to change column name. User can rename the column using alter table rename statement.
If user does not need the Gender and fullname column from customer table then user needs to use Alter table drop statement.įullname varchar2 (60)) Alter table rename column: Also to drop the specific column user needs to use Alter table drop statement.ĭrop(Column_name1 (datatype size) Column_Constraint1, Sometimes there is requirement where user needs to drop the specific column. 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. 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. There should be different scenarios user always face and tackle using modify statement. Modify (Column_name1 (datatype size) Column_Constraint1, For any kind of modification Alter table modify statement is used. User can add the constraint also using the modify statement. So to modify the column Alter table modify statement is used. Many times user facing issue of size of the variable. User can achieve this using only one Alter statement.Īlter table Modify column for modifying the size of column: First column is Gender, which has not null constraints and second is fullname column. If there is a requirement to add 2 columns in customer table. The syntax is quite different :Īdd (Column_name1 (datatype size) Column_Constraint1,Ĭolumn_name2 (datatype size) Column_Constraint2,Ĭolumn_name’n’ (datatype size) Column_Constraint’n’) Using only one alter statement user can add multiple columns in the table. Sometimes business requirement needs to add multiple columns in table. Alter table add column for adding multiple columns: Note : Same Syntax is used to add the columns in oracle, MySQL, Microsoft sql server and PostgreSQL.
So to add the columns with constraints following syntax is used:Īdd Column_name (datatype size) Column_Constraint Ĭonsider user wants to add the column in product table, which have ‘not null’ constraint.Īdd Product_Sub_Type Varchar2 (30) not null There are some requirements where user needs to add the columns with constraints like IFNULL or Check constraint. If Product table have product type but as per requirement product table needs to add product subtype also. When there is a requirement, where user needs to add the column without constraints following syntax is helpful:Ĭonsider reporting environment and one report needs to add one extra column in product table.
Column with constraints and column without constraints. Alter table add column without Constraints: 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 statement is used to modify the structure of the table and used to add the column to the table. Alter table statement is used to change the structure of the table. Most of developers needs to add the different columns for different tables as per business requirements. This article will help the developers to alter the table and add columns in oracle sql. In my previous article I have explained about different examples of create statement.
0 kommentar(er)
