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 :

1 comments:

  1. 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