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 and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com
ReplyDeleteOracle 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