SQL alter used to change
the structure of table
ADD COLUMN IN TABLE |
Used to add new column to existing table.
Syntax:
Alter table <table_name> add <col datatype>;
Example:
ALTER TABLE student ADD
phone NUMBER;
ALTER TABLE student ADD (phone1
NUMBER,
email VARCHAR2(10) );
MODIFY COLUMN IN TABLE |
Used to change the data type of existing column
Syntax:
Alter table <table_name> modify <col datatype>;
Example:
ALTER TABLE student MODIFY name VARCHAR2(50);
ALTER TABLE student MODIFY (name VARCHAR2(100),
email VARCHAR2(100));
Note: To reduce the data
type size, it will support only on empty table
RENAME COLUMN IN TABLE |
Used to change the existing column name
Syntax:
Alter table <table_name> rename column <old_col_name> to
<new_col_name>;
Example:
ALTER TABLE student RENAME COLUMN name TO
sname;
DROP COLUMN IN TABLE |
Used to remove column from existing table
Syntax:
Alter table <table_name> drop <col datatype>;
Example:
ALTER TABLE student DROP COLUMN srno;
ALTER TABLE student DROP (phone,email);
OPERATIONS WITH CONSTRAINTS |
Possible operations with constraints as follows.
Enable
Disable
Enforce
DropENABLE CONSTRAINTS |
This will enable the constraint. Before enable, the constraint will check the existing data.
Example:
SQL>ALTER TABLE student ENABLE CONSTRAINT un;DISABLE CONSTRAINTS |
This will disable the constraint.
Example:
SQL>ALTER TABLE student DISABLE CONSTRAINT un;
ENFORCE CONSTRAINTS |
This will enforce the constraint rather than enable for future inserts or updates.
This will not check for existing data while enforcing data.
Example:
SQL>ALTER TABLE student ENFORCE CONSTRAINT un;
DROP CONSTRAINTS |
This will remove the constraint.
Example:
SQL>ALTER TABLE student DROP CONSTRAINT un;
Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.
ReplyDelete