alter table
The ALTER TABLE
command in Oracle Database is used to modify the structure of an existing table. You can use it to add, modify, or drop columns, constraints, or even rename the table itself.
1. Syntax of ALTER TABLE
ALTER TABLE table_name
[ADD column_definition]
[MODIFY column_definition]
[DROP COLUMN column_name]
[RENAME COLUMN old_column_name TO new_column_name]
[ADD CONSTRAINT constraint_definition]
[DROP CONSTRAINT constraint_name]
[RENAME TO new_table_name];
2. Key Use Cases of ALTER TABLE
A. Adding a Column
You can add one or more columns to an existing table.
Syntax:
ALTER TABLE table_name ADD column_name datatype [constraint];
Example:
ALTER TABLE EMP1 ADD phone_number VARCHAR2(15);
B. Modifying a Column
You can modify the data type, size, or constraints of an existing column.
Syntax:
ALTER TABLE table_name MODIFY column_name new_datatype [constraint];
Example:
ALTER TABLE EMP1 MODIFY NAME VARCHAR2(30) NOT NULL;
C. Dropping a Column
You can drop a column from the table (starting from Oracle 8i).
Syntax:
ALTER TABLE table_name DROP COLUMN column_name;
Example:
ALTER TABLE EMP1 DROP COLUMN phone_number;
D. Renaming a Column
Rename an existing column.
Syntax:
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
Example:
ALTER TABLE EMP1 RENAME COLUMN NAME TO full_name;
E. Adding a Constraint
You can add a new constraint to the table.
Syntax:
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;
Example:
ALTER TABLE EMP1 ADD CONSTRAINT email_unique UNIQUE (EMAIL);
F. Dropping a Constraint
You can drop an existing constraint by its name.
Syntax:
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Example:
ALTER TABLE EMP1 DROP CONSTRAINT PM_KEY;
G. Renaming the Table
You can rename the table itself.
Syntax:
ALTER TABLE table_name RENAME TO new_table_name;
Example:
ALTER TABLE EMP1 RENAME TO employees_data;
3. Real-World Examples
Example 1: Adding Multiple Columns
ALTER TABLE EMP1
ADD (
address VARCHAR2(100),
date_of_birth DATE
);
Example 2: Changing Data Type of a Column
ALTER TABLE EMP1 MODIFY salary NUMBER(12, 2);
Example 3: Adding and Dropping Constraints
-- Add a foreign key constraint
ALTER TABLE EMP1
ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id);
-- Drop the foreign key constraint
ALTER TABLE EMP1 DROP CONSTRAINT fk_department;
Example 4: Renaming a Table and Column
-- Rename the table
ALTER TABLE EMP1 RENAME TO employee_records;
-- Rename a column in the table
ALTER TABLE employee_records RENAME COLUMN email TO email_address;
4. Key Points
- Constraints: Constraints like
NOT NULL
,UNIQUE
,PRIMARY KEY
, andFOREIGN KEY
can be added or dropped usingALTER TABLE
. - Data Loss: Dropping columns or constraints can result in data loss, so use caution.
- Performance: Modifying a large table structure (e.g., adding or resizing columns) can temporarily lock the table and affect performance.
- Rollback: Structural changes made with
ALTER TABLE
cannot be rolled back.
5. Validate Changes
You can verify changes using these commands:
- Check Table Structure:
DESC EMP1;
- Check Constraints:
SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = 'EMP1';
Comments
Post a Comment