ALTER TABLE in Oracle

previous-topic-oraclemine                                                                                                   next-topic-oraclemine

ALTER TABLE in Oracle is used to modify table structure by adding or deleting columns, add and drop constraints to the table, change the length of column datatypes, drop and rename table columns and rename the table. It is classified as DDL (Data Definition Language) statement.

ALTER TABLE

Syntax of ALTER TABLE

ALTER TABLE table_name 
ADD column_name1 datatype |
ADD CONSTRAINT constraint_name |
MODIFY (column_name1 datatype, column_name2 datatype…column_name_n datatype) |
DROP [COLUMN] column_name1 |
DROP {PRIMARY KEY | FOREIGN KEY constraint_name | 
      UNIQUE constraint_name | CHECK constraint_name | 
      CONSTRAINT constraint_name} |
RENAME COLUMN old_columnname TO new_columnname;
Parameters Explanation

table_name

  • Name of the table you want to create

column_name1, column_name2… column_name_n, old_columnname, new_columnname

  • Name of the columns you want to add, modify or rename in the table

datatype

  • Datatype of each column

constraint_name

Example of ALTER TABLE

  • Add new column dept_id in table employees
ALTER TABLE employees ADD dept_id NUMBER(10);
  • Increase length of column salary from 10 to 20
ALTER TABLE employees MODIFY(salary NUMBER(20);
  • Drop column city from employees table
ALTER TABLE employees DROP COLUMN city;
  • Add unique constraint in column national_identifier in employees table
ALTER TABLE employees ADD CONSTRAINT NI_UC UNIQUE(national_identifier);
  • Drop constraint NI_UC from employees table
ALTER TABLE employees DROP CONSTRAINT NI_UC;
  • To rename column empno to employee_number in employees table
ALTER TABLE employees RENAME empno TO employee_number;
  • To rename table emp to employees
ALTER TABLE emp RENAME TO employees;

I hope you like the page and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of OracleMine.com.

previous-topic-oraclemine                                                                                                   next-topic-oraclemine

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.