Monday , September 23 2019
Home / SQL / Add, Modify, Drop, Rename Table Column by ALTER TABLE statement

Add, Modify, Drop, Rename Table Column by ALTER TABLE statement

The ALTER TABLE statement is used to alter the definition of a table in oracle database. Suppose, if you want to add a new column, change or modify  a column or delete a column in existing table which already created by CREATE TABLE statement, then you can use ALTER TABLE statement. You can also rename a table by ALTER TABLE statement.

To complete the practice, first create a table named EMPLOYEE in insert four rows.

CREATE TABLE Employee

(

Employee_Id                 varchar2(10),

Employee_Name          varchar2(100),

City                                   varchar2(200),

Join_Date                       date

);

INSERT INTO Employee VALUES (‘EM-101′,’Milon Chowdhury’,’Mirpur-1,Dhaka’,’11-APR-2005′);

INSERT INTO Employee VALUES (‘EM-102′,’Abdur Rahman’,’Mowna,Gazipur’,’02-MAY-2009′);

INSERT INTO Employee VALUES (‘EM-103′,’Abul Hossain,’Bhaluka,Mymensingh’,’31-MAR-2006′);

INSERT INTO Employee VALUES (‘EM-104′,’Raihan’,’Sripur,Gazipur’,’02-NOV-2007′);

COMMIT;

SELECT * FROM Employee;

DESCRIBE Employee;

In the above statements, four columns have been inserted and commit to save data permanently. Then table structure has been view by DESCRIBE command.

Now go to implement the following commands along with ALTER TABLE statement to change the table structure.

ADD COLUMN:

You can add new column in existing oracle table by ALTER TBLE statement.

 Syntax:

ALTER   TABLE  table_name  ADD  column_name   datatype(length);

Example:

ALTER   TABLE   Employee   ADD   salary  number (10,2);

In the above example, the new column named SALARY has been added in existing EMPLOYEE table. Execute the following DESCRIBE command to see the altered table structure.

DESCRIBE Employee;

The new structure of EMPLOYEE table is given below

Name                            Null?      Type

——————————- — —  – — —-

EMPLOYEE_ID                             VARCHAR2(10)

EMPLOYEE_NAME                      VARCHAR2(100)

CITY                                                   VARCHAR2(200)

JOIN_DATE                                     DATE

SALARY                                             NUMBER(10,2)

 MODIFY COLUMN:

You can modify existing column of an oracle table by ALTER TBLE statement.

Syntax:

ALTER   TABLE   table_name   MODIFY  column_name   modification_unit;

Example:

ALTER    TABLE     Employee      MODIFY       Employee_Id  varchar2(10)   NOT   NULL;

In the example,  Employee_Id   column has been modified by setting NOT NULL.

Execute the command and compare the two structures that are before and after modification.

DESCRIBE Employee;

Before modification:

Name                            Null?            Type

——————————- ——– ———————-

EMPLOYEE_ID                                  VARCHAR2(10)

EMPLOYEE_NAME                           VARCHAR2(100)

CITY                                                        VARCHAR2(200)

JOIN_DATE                                          DATE

SALARY                                                   NUMBER(10,2)

 After modification:

Name                            Null?                       Type

——————————- ——– —————————

EMPLOYEE_ID             NOT NULL        VARCHAR2(10)

EMPLOYEE_NAME                                  VARCHAR2(100)

CITY                                                              VARCHAR2(200)

JOIN_DATE                                                DATE

SALARY                                                        NUMBER(10,2)

From the above two structures, we see that EMPLOYEE_ID   column was null able. So, the column is allowing null values. But after executing ALTER TABLE statement the EMPLOYEE_ID  column has been modified by NOT NULL. So, now EMPLOYEE_ID   column is showing NOT NULL  under Null? Column of the DESCRIBE command output. And also this column will not allow NULL values now.

DROP COLUMN:

You can drop a column from oracle table by ALTER TBLE statement.

Syntax:

ALTER   TABLE    table_name   DROP   COLUMN   column_name;

Example:

ALTER    TABLE    Employee   DROP   COLUMN    city;

In the above example, the CITY column has been dropped from EMPLOYEE table.

RENAME COLUMN NAME  IN TABLE:

You can rename a column name of a table by ALTER TBLE statement.

Syntax:

ALTER    TABLE     table_name

RENAME   COLUMN   column_name   TO   new_column_name;

Example:

ALTER    TABLE    EMPLOYEE

RENAME   COLUMN    salary    TO    salary_amount;

In the above example, the SALARY column of the EMPLOYEE table has been renamed to the new name SALARY_AMOUNT by ALTER TABLE statement.

RENAME TABLE NAME:

You can rename a table name by ALTER TBLE statement.

Syntax:

ALTER TABLE tablename RENAME TO new_tablename;

Example:

ALTER TABLE EMPLOYEE RENAME TO EMP;

In the example, the EMPLOYEE table has been renamed to EMP by ALTER TABLE statement.

Check Also

Unique Constraint

Definition and Set UNIQUE constraint by CREATE TABLE statement in Oracle

Definition of UNIQUE constraint: The UNIQUE constraint is used to ensure unique or distinct values …

Set NOT NULL constraint by ALTER TABLE statement in Oracle

You can set NOT NULL constraint on existing table column by ALTER TABLE statement. Practice: …

One comment

  1. Great line up. We will be linking to this great article on our site. Keep up the good writing.

Leave a Reply

Your email address will not be published. Required fields are marked *