Structure Modification,Updation and Deletion
Previous:
Relationships in a RDBMS
It is possible to modify the structure of a table (the relation schema) even if rows have already been inserted into the table.
Adding the Column to the Table: - A column can be added using the alter table command. Following is the syntax for adding the column to the table.
Syntax: - alter table <table name> add(<column> data type [<default value>] [<column constraint>]);
If more than one column should be added at one time, respective add clauses need to be separated by colons.
A table constraint can be added to a table using
alter table <table name> add (<table constraint>);
(Note that a column constraint is a table constraint, too. not null and primary key constraints can only be added to a table if none of the specified columns contains a null value.)
Example: - Suppose you want to add a new column Contact_Number to the Student table in the previous example.
You can add the column using the following statement: -
alter table Student add(Contact_Number Number(10));
Modifying the Column of the Table: - A column can be modified using the alter table command. Following is the command for modifying the column attributes: -
Syntax: - alter table <table name> modify(<column> [<data type>] [<default value>] [<column constraint>]);
Example: - If you want to change the size of TeacherName in the Teacher table. You can do it with the following statement:-
alter table Teacher modify(TeacherName varchar2(35));
Deleting a Table: - A table and its rows can be deleted by issuing the drop table command. Following is the syntax for deleting a table.
Syntax: - drop table table name [cascade constraints];
Example: - If you want to delete the Marks table. You can delete it using the following statement:-
drop table Marks;
Updating the Table: - For modifying attribute values of (some) tuples in a table, we use the update statement. The following is the syntax for Update:
Syntax: - update <table name> set <column i> = <expression i>, . . . , <column j> = <expression j> [where <condition>];
An expression consists of either a constant (new value), an arithmetic or string operation, or an SQL query. Note that the new value to assign to column i must be the matching data type. An update statement without a where clause results in changing respective attributes of all tuples in the specified table. Typically, however, only a (small) portion of the table requires an update.
