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.
Comments