Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Data Definition Language (DDL) – Part IX

Posted by Ravi Varma Thumati on October 13, 2009

Data Definition Language

The data definition language is used to create an object, alter the structure of an object and also drop already created object. The Data Definition Languages used for table definition can be classified into following:

  • Create table command
  • Alter table command
  • Truncate table command
  • Drop table command

Creation of Table

Table is a primary object of database, used to store data in form of rows and columns. It is created using following command:

 Create Table <table_name>

(

column1 datatype(size), column2 datatype(size),

………,column(n) datatype(size)

);

 Where, table_name is a name of the table and coulumn1, column2 … column n is a name of the column available in table_name table.

v       Each column is separated by comma.

 Pointes to be remember while creating a table.

  • Table Name must be start with an alphabet.
  • Table name and column name should be of maximum 30 character long.
  • Column name should not be repeated in same table.
  • Reserve words of Oracle cannot be used as a table and column name.
  • Two different tables should not have the same name.
  • Underscores, numerals and letters are allowed but not blank space or single quotes.

Example

 SQL> Create Table Student

(

rollno number(5),name varchar2(25),course_name varchar2(20),

birth_date date, fees number(9,2)

);

 Table Created.

 SQL>Create Table Emp_Master(

Empno number(5),ename varchar2(25),job

varchar2(20),hiredate date,salary number(7),deptno number(4));

 Table Created.

v       Semicolon is used as a terminator. Every SQL command ends with a semicolon. Without semicolon compiler won’t execute the command.

Above definition will create simple table. Still there are more additional option related with create table for the object-relation feature we will discuss it afterwards.

Desc command

Describe command is external command of Oracle. The describe command is used to view the structure of a table as follows.

Desc <table name>

Example

SQL> desc emp_master

 desc1

 Alteration of Table

Once Simple Table is created, if there is a need to change the structure of a table at that time alter command is used. It is used when a user want to add a new column or change the width of datatype or datatype itself or to add or drop integrity constraints or column. (we will see about constraints very soon.). i.e. table can be altered in one of three way : by adding column, by changing column definition or by dropping column.

Addition of Column(s)

Addition of column in table is done using:

Alter table <table_name> add(column1 datatype, column2 datatype ………);

Add option is used with “alter table” when you want to add a new column in existing table. If you want to add more than one column then just write column name, data type and size in brackets. As usual Comma sign separates each column.

For Example, suppose you want to add column comm in emp_master, then you have to perform the following command.

SQL>Alter Table Emp_master add comm number(7,2);

Table altered.

If command performs successfully it will add comm. column in emp_master

v       We can add multiple columns in a single command.

After adding column table structure is changed as per follows

 SQL> desc emp_master

 desc

 Deletion of Column

Till Oracle8 it is not possible to remove columns from a table but in Oracle8i, drop option is used with “alter table” when you want to drop any existing column.

Alter table <table_name> drop column <column name>;

Using above command you cannot drop more than one column at a time.

For Example, suppose you want to delete just before created column comm from the emp_master, then you have to apply following command.

SQL>Alter Table Emp_master drop column comm;

Table altered.

Dropping column is more complicated than adding or modifying a column, because of the additional work that Oracle has to do. Just removing the column from the list of columns in the table actually recover the space that was actually taken up by the column values that is more complex, and potentially very time consuming for the database. For this reason, you can drop a column using unused clause. Column can be immediately remove column by drop clause, the action may impact on performance or one make marked column as unused using unused caluse, there will be no impact on performance. When unused clause is used the column can actually be dropped at a later time when the database is less heavily used. One can marked column as a unused using :

Alter table <table_name> set unused column <column name>;

For Example,

SQL>Alter Table Emp_master set unused column comm;

Table altered.

Making a column as “unused” does not release the spcace previously used by the colum, until you drop the unused columns. It can be possible using:

Alter table <table_name> drop unused columns;

v      Once you have marked column as “unused” you cannot access that column

You can drop multiple columns at a time using single command as per follows

Alter table <table_name> drop (Column1, Column2,…);

The multiple columns name must be enclosed in parentheses.

Modification in Column

Modify option is used with “alter table” when you want to modify any existing column. If you want to modify data type or size of more than one column then just write column name, data type and size in brackets and each column is separated by comma sign as per follows:

Alter table <table name> modify (column1 datatype,………);

For Example, if you want to change size of salary column of emp_master the following command is performed.

SQL> Alter table emp_master modify salary number (9, 2);

Table altered.

It will change size of salary column from 7 to (9, 2).

When you want to decrease the size of column, table must be empty. If table has any rows then it will not allow decrement in the column width.

Truncate Table

If there is no further use of records stored in a table and the structure is required then only data can be deleted using Truncate command. Truncate command will delete all the records permanently of specified table as follows.

Truncate table <table name> [Reuse Storage];

Example

Following command will delete all the records permanently from the table.

SQL>Truncate Table Emp_master;

Or

SQL>Truncate Table Emp_master Reuse Storage;

Table truncated.

Drop Table

If the table is no longer in use, drop command will drop table data and table structure both as follows:

Drop table <table name>;

For example, to drop emp_master table, the following command is used.

SQL>drop table emp_master;

Table dropped.

All the data definition commands are auto-committed. Once command is performed is cannot be rollback.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: