Table of Contents
hide
Alter Table Statement
Definition :
- Alter Table statement is used to change the structure of an existing table of a database i.e. this statement specifies how to add one or multiple new columns/fields/attributes, modify one or more existing columns/fields/attributes , drop or delete the existing columns from a table, rename the existing columns in an existing table.
- It is also used to rename a table.
Syntax:
(A) To change the structure of an existing table :-
(I) To add one or multiple new columns/fields/attributes in a created table
ALTER TABLE table_name ADD
(column_name1 data_type(size) constraint or column definition,
column_name2 data_type(size) constraint,
column_name3 data_type(size) constraint
.
.
.
);
(II) To modify one or more existing columns/fields/attributes in a created table
ALTER TABLE table_name MODIFY
(column_name1 data_type(size) constraint or column definition,
column_name2 data_type(size) constraint,
column_name3 data_type(size) constraint
.
.
.
);
(III) To drop or delete or remove the existing columns/fields/attributes from an existing table
ALTER TABLE table_name DROP COLUMN
(column_name1,
column_name2,
column_name3
.
.
.
);
(IV) To rename the existing column/field/attribute in an existing table
ALTER TABLE table_name RENAME COLUMN
(old column_name1 TO new column_name1,
old column_name2 TO new column_name2,
old column_name3 TO new column_name3
.
.
.
);
NB : Size and Constraint should be used wherever applicable in a field.
(B) To rename an existing Table :-
ALTER TABLE table_name
RENAME TO new _table_name;
Examples :
Example : How to add one or multiple new fields/columns in an existing table of an Oracle Database?
ALTER TABLE Employee ADD(Dept_name varchar2(30) not null, Post_name varchar2(40) not null, Salary Number(15) not null); (Press Enter)Example : How to Modify or Change one or multiple existing fields/columns in an existing table of an Oracle Database?
ALTER TABLE Employee MODIFY(Dept_name varchar2(50) not null, Post_name varchar2(40) not null, Salary Number(10) null); (Press Enter)Example : How to Drop or Delete one or multiple existing fields/columns from an existing table of an Oracle Database?
ALTER TABLE Employee DROP COLUMN(Dept_name, Post_name, Salary); (Press Enter)Example : How to Rename one or multiple existing fields/columns/attributes in an existing table of an Oracle Database?
ALTER TABLE Employee RENAME COLUMN(Dept_name TO DName, Post_name TO PName, Salary TO Sal); (Press Enter)Example : How to Rename an existing table in an Oracle Database?
ALTER TABLE Employee RENAME TO Emp; (Press Enter)
Drop Table Statement
Definition :
- This statement is used to remove or delete an existing table from the Oracle database.
Syntax :
- DROP TABLE Table_name; (Press Enter)
- DROP TABLE Database_name.Table_name; (Press Enter)
-
DROP TABLE [Schema_name or Database_name].Table_name
[CASCADE CONSTRAINTS]
[PURGE];
Here, Cascade Constraints are optional and when applied with query then all the linked referential integrity constraints will be dropped as well.
Here, Purge is optional field and when applied then the table and its dependent objects will be purged from the recycle bin and we will not be able to recover the table further in the future. If not used with query, the table and its dependent objects are placed in the recycle bin and can be recovered later anytime, if needed.
Examples :
- DROP TABLE Employee; (Press Enter)
0 Comments