Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Tuesday, 4 April 2017

SQL Table Alter




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
                               Drop



ENABLE 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;

Once the table is dropped, constraints automatically will drop.
Share this article :

2 comments:

  1. Oracle apps and Fusion Self Paced Training Videos by Industry Experts. Please Check oracleappstechnical.com

    ReplyDelete
  2. 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