Header Ads Widget

Alter Table Statement in SQL [ Complete Detail at one place ]

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

So we will learn about alter table syntax in SQL and alter table syntax in Oracle.
SQL - ALTER TABLE Command. The SQL ALTER TABLE command is used to add, delete or modify columns in an existing table. You should also use the ALTER TABLE command to add and drop various constraints on an existing table. 
SQL ALTER Table Syntax
SQL ALTER Table Syntax


Q. How to change column datatype in SQL database without losing data ?
  Answer: You can do it very easily with simple command. Command to change column datatype in SQL database is as follow. 


ALTER TABLE table_name MODIFY column_name column_type;
Q. How to add multiple columns in oracle SQL ?
     To ADD MULTIPLE COLUMNS to an existing table, the Oracle ALTER TABLE syntax is
ALTER TABLE table_name ADD (column_1 column_definition, column_2 column_definition, ... column_n column_definition);
Q. How to rename a table column in oracle SQL ?
ALTER TABLE table_name MODIFY column_name
 Q. How to DROP a column in oracle SQL ?

       To DROP A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

   ALTER TABLE table_name DROP COLUMN column_name;

Q. How to add a not null column to already existing table having data in oracle SQL ?

To achieve this we need to first add that column without any constraint. If we will add a column using Alter Sql statement with not null constraint, then DB will not allow you to add that column. Whenever we add a new column then that column will be null initially.. So if we are adding any constraint while adding that column, system will not allow a not null column to be added without any data.

Syntax 
Step 1.  ALTER TABLE table_name ADD column_name column_definition;

Step 2.  Update this column with data. None if the cell should be null.

Step 3.  ALTER TABLE table_name MODIFY column_name column_definition NOT NULL;


So these were the basic ALTER statements to alter/modify tables and its columns in SQL. Next Post will be on DROP Table.
Please write to me if you need any help regarding SQL topics.
My contacts are : rikyosinha@gmail.com

Post a Comment

0 Comments