top of page
Writer's picturecompnomics

Basics ALTER TABLE Command in Oracle SQL


Introduction

The ALTER TABLE command in Oracle SQL is a powerful tool for modifying the structure of existing tables. It allows you to add, drop, or modify columns, change data types, and more. In this blog post, we'll explore the various uses of ALTER TABLE and provide examples using a sample STUDENTS table.


Our Sample Table: STUDENTS

Column Name

Data Type

STUDENT_ID

NUMBER(10)

STUDENT_NAME

VARCHAR2(50)

ADDRESS

VARCHAR2(100)

PHONE

VARCHAR2(20)

CITY

VARCHAR2(50)

DISTRICT

VARCHAR2(50)

STATE

VARCHAR2(50)

Syntax

The basic syntax of the ALTER TABLE command is:

SQL

ALTER TABLE table_name
[ ADD column_name data_type [ constraints ] ]
[ DROP COLUMN column_name ]
[ MODIFY column_name data_type [ constraints ] ]
[ RENAME COLUMN old_name TO new_name ]
[ ADD CONSTRAINT constraint_name constraint_type (column_list) ]
[ DROP CONSTRAINT constraint_name ]
[ ENABLE CONSTRAINT constraint_name ]
[ DISABLE CONSTRAINT constraint_name ]
[ MOVE TABLE storage_clause ]
[ EXCHANGE PARTITION partition_name WITH TABLE other_table ]
[ ... ]

Example: Adding a Column

To add a new column named EMAIL to the STUDENTS table:

ALTER TABLE STUDENTS
ADD EMAIL VARCHAR2(100);

Example: Dropping a Column

To remove the DISTRICT column from the STUDENTS table:

ALTER TABLE STUDENTS
DROP COLUMN DISTRICT;

Example: Modifying a Column

To change the data type of the PHONE column to NUMBER(10):

ALTER TABLE STUDENTS
MODIFY PHONE NUMBER(10);

Example: Renaming a Column

To rename the ADDRESS column to CURRENT_ADDRESS:

ALTER TABLE STUDENTS
RENAME COLUMN ADDRESS TO CURRENT_ADDRESS;

Example: Adding a Constraint

To add a unique constraint on the STUDENT_ID column to ensure it has unique values:

ALTER TABLE STUDENTS
ADD CONSTRAINT STUDENT_ID_UK UNIQUE (STUDENT_ID);

Example: Dropping a Constraint

To remove the unique constraint on the STUDENT_ID column:

ALTER TABLE STUDENTS
DROP CONSTRAINT STUDENT_ID_UK;

Example: Enabling/Disabling a Constraint

To temporarily disable the STUDENT_ID_UK constraint:

ALTER TABLE STUDENTS
DISABLE CONSTRAINT STUDENT_ID_UK;

To re-enable it:

ALTER TABLE STUDENTS
ENABLE CONSTRAINT STUDENT_ID_UK;

Additional Features

  • Moving Tables: You can move a table to a different storage location using the MOVE TABLE clause.

  • Exchanging Partitions: You can exchange a partition of a partitioned table with a table of the same structure.


Remember to use caution when modifying table structures, especially in production environments. Always test your changes on a development or test database before applying them to your production data.

10 views0 comments

Recent Posts

See All

Comments

Rated 0 out of 5 stars.
No ratings yet

Add a rating
bottom of page