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.