ALTER COMMAND

Altering A Table Structure


After creating the table we can change the data type of a column or change the size of a column or add a new constraint or delete a constraint or add a new column or delete a column by using Alter Table command. Syntaxes of Alter table command are as follows.

To change the datatype of a column or to change the size of a column or to add not null constraint to a column, the following syntax is used.

Syntax:

Alter table tablename modify
(Col1 Datatype Not Null,
Col2 Datatype Not Null,
………..
Col n Datatype Not Null
);

Example : The following example increases the size of Sname column in Student table to 20 and adds not null constraint to fee column.
Alter table Student modify (Sname varchar2(20), Fee number(7,2) Not Null );

Restrictions:

Changing the datatype of a column is not possible if that column contains data.
Adding not null constraint is not possible if that column contains NULL values.
Decreasing the Size of a column is not possible if that column contains data.


To add a new column to the table, the following syntax is used.

Syntax:
Alter table tabname ADD
(Col1 Datatype Constraint Consname Consspec,
Col2 Datatype Constraint Consname Consspec,
.
.
.
Col n Datatype Constraint Consname Consspec);

Example : The following example adds a new column with name Fname (Father’s name) to Student table.

Alter table Student ADD (Fname Varchar2(10));

Restrictions :

a) Adding a column with primary key or Not Null constraint is not possible if the table contains data.


To add a constraint to a table, the following syntax is used.


Syntax:
Alter table tabname ADD Constraint consname consspec( Colname );

Example 1: The following example adds Primary Key constraint to Sno Column of Student table.
Alter table Student ADD constraint Sno_PK Primary Key (Sno);

Example 2: The following example adds Foreign Key constraint to Sno Column of Marks table.
Alter table Marks ADD constraint Sno_FK Foreign Key (Sno) references Student(Sno);

Restrictions:

Adding any constraint to a column is not possible if that column contains data that violates that constraint.
Adding Not Null constraint is not possible by using this syntax.


To disable and enable constraints, the following syntax is used.
Syntax:
Alter table tabname Disable/Enable constraint consname;

Example: The following example disables the foreign key constraint on Sno column of Marks table.
Alter table Marks Disable constraint Sno_FK;

Restrictions:

Disabling a primary key is not possible if that Primary key is refered by a foreign key. To delete primary key by deleting the foreign key constraint automatically, we have to use cascade constraints clause at the end of the alter table command.
Enabling a constraint is not possible if that column contains data that violates the constraint.

To delete a constraint or a column, the following syntax is used.

Syntax:
Alter table tabname Drop Column/Constraint Col.Name/ConsName;

Example 1: The following example deletes Foreign Key constraint on Sno column of Marks table.
Alter table Marks Drop constraint Sno_FK;

Example 2: The following example deletes Fee column from Student table.
Alter table Student Drop Column Fee;

Example 2: The following example deletes Fee, Fname columns from Student table in a sinle alter table statement.
Alter table Student Drop (Fee, Fname);


Restrictions:

Deleting a Primary key constraint or primary key column is not possible if that Primary key is refered by a Foreign Key. To delete primary key column by deleting the foreign key constraint automatically, we have to use cascade constraints clause at the end of the alter table command.

We can make a column as unused. Making a column unused does not release the space previously used by the column until you drop the unused columns.

Syntax:
Alter table tabname set unused column ColumnName;

Example : The following example sets total column of marks table unused.
Alter table Marks set unused column total;

To delete all unused columns of a table, drop unused columns clause is used as follows.

Example : Alter table Marks drop unused columns;