Alter Table Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

The alter statement can be used for one of the following reasons:

1. A column needs some particular constraint or a constraint on a column needs to be dropped.


2. Data Type or Size of a column needs to be changed.


3. A column name of a table needs to be changed or table name itself needs to be changed.


4. Some columns need to be dropped or added to the table.

Here we can’t drop the table because the table is already loaded and its data is referred by some other object.

To overcome these issues alter table command is useful. Before looking into those Alter table commands let us first create one test table namely ‘employee’.

Create a Dummy Table i.e. “Employee”

CREATE TABLE employee

(   EMP_ID          NUMBER

   ,EMPLOYEE_NAME   VARCHAR2(30)

   ,AGE             NUMBER

   ,SALARY          NUMBER(5)

   ,JOB_TITLE       VARCHAR2(30)

);

Insert Records into Dummy Table i.e. “Employee”

INSERT INTO employee VALUES(101,'John',25,20000,'Marketing Executive') ;

INSERT INTO employee VALUES(102,'Smith',28,12000,'Service Technician') ;

INSERT INTO employee VALUES(103,'Jackson',23,15000,'Manager') ;

INSERT INTO employee VALUES(104,'Scott',30,11000,'Developer') ;

INSERT INTO employee VALUES(105,'Herison',26,13000,'Purchage Officer') ;

Select * from employee;

Fetch the Data from Dummy Table i.e. “Employee”

[-] To add a constraint to a table column 

Syntax:    ALTER TABLE <table_name> MODIFY <column_name>  <constarint> ;

Example   ALTER TABLE employee MODIFY emp_id NOT NULL;

[-] To drop a constraint on a table column 

Syntax:    ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name> ;                  

                (without quotes)

Example:  ALTER TABLE employee DROP CONSTRAINT SYSC120000;     

         Note:

1.      All the system defined constraints starts with 'SYS_.'.

2.      If constraint name is not known then first find out the name of constraint on a column.

Syntax:

SELECT *   FROM all_constraints  WHERE table_name = UPPER(&table_name)

                Example:

             SELECT *  FROM all_constraints  WHERE table_name = UPPER('employee') ; 

[-]  To drop a column from a table  

         Syntax:  ALTER TABLE <table_name>

                      DROP (<column1, column2, column3, column4...>) ;

Example:

             ALTER TABLE employee DROP (emp_name, emp_address) ; 

[-]  To add a new column to a table 

Syntax:    ALTER TABLE <table_name> ADD <column_name and datatype> ;

Example: ALTER TABLE employee ADD emp_name VARCHAR2(15) ; 

[-] To change the column name of a table 

Syntax: ALTER TABLE <table_name>

            RENAME COLUMN <old_column_name> TO <new_column_name> ;

Example:

              ALTER TABLE employee RENAME COLUMN emp_id TO employee_id ; 

[-] To rename an existing table name 

Syntax:    RENAME <old_table_name> TO <new_table_name>;

Example: RENAME employee TO employee1; 

There is one more syntax for this 

Syntax:   ALTER TABLE <old_table_name> RENAME TO <new_table_name> ;

Example: ALTER TABLE employee RENAME TO employee1; 

[-] To change the data type of a column 

Syntax:  ALTER TABLE <table_name>

              MODIFY <column_name> <new_data_type and size> ;

Example:  ALTER TABLE employee MODIFY employee_name VARCHAR2(20) ; 

Note:  

1. DATA TYPE of a column can not be changed until the column is having values populated i.e. to change the DATA TYPE column should not have NULL values for all the records


2. Column SIZE always can be reduced to the maximum size of data present in that column.


3. There is no restriction on increase in SIZE of a column.
   
   

• Printer Friendly
• Email to a friend
• Add to my Favourites
• Download PDF version
• Report Bad Submissions
• Submit Feedback