Data definition language (DDL)7 min read

A database scheme is specified by a set of definitions which is expressed by a special language called a data definition language (DDL). It is used to create, remove, or altering, the structure of table or database objects. It includes the following statements/operations,

1) Creation of Table
2) Altering Table
3) Dropping Table
4) Truncating Table

Data definition language (DDL)
Data Definition Language

If you want to learn more about the Structured Query Language (SQL) click here


Data Dictionary

The result of a compilation of data definition language statements is a set of tables that are stored in a special file called a data dictionary. A data dictionary is a file that contains “metadata which stores data about data. Whenever data is read or modified in the database system, the data dictionary is consulted first.

Rules to define a Table / Naming Table:

  • Following rules should be followed while defining a table.
  • Table names must be unique.
  • The table name can not exceed 30 characters.
  • Must begin with an alphabet followed by characters A-Z, numbers 0-9, and special character $, #, and _ (Underscore).
  • Table name should not be an Oracle keyword.
  • The table name can be in uppercase or lowercase. If the table name is included within the quote (” “) then
  • Table name becomes case sensitive, an1st letter of table name may be a digit. Some important points one must know regarding the column of a table.
  • All columns must have a unique name in the table.
  • The size of the column can not be decreased if the table contains a value(s).
  • A column name can not be renamed.

Note

  • . Only one column in a table can be the primary key.
  • . Only one column in a table can have a long data type. This should not contain a unique or primary key and the column can not be indexed.

Creating a table

Before manipulating data into a table, a table must be defined with the CREATE TABLE command. Thus the basic purpose of the creation of a table is to store data and it is the only way to perform the bus operations on data. When tables are created, three parameters are generally supplied for each column and they are Data Definition Language.

1) Names of Column.
2) Its Datatype and
3) Maximum Size of each Column.

Each table must have at least one column. A new column easily be added later or the size of the column can be increased but one can not rename the column. For date data type, size is not required.

Commas separate columns in the list. Before issuing a comma be sure that the name, data type, and size for the 1st column has been correctly specified before the next column is to be defined. If one tries to create a table with the same name which is previously created then Oracle display the following error message,

“Name is already used by an existing object”. The only way to create a table with the same name is to drop the previous table command using the drop table command.

Syntax :

CREATE TABLE table_name
(Column1 datatype [(size)] [column constraint],
(Column2 datatype [(size)] [column constraint],
..................
(Column2 datatype [(size)] [column constraint],
[Table Constraint]
)

Ex 1. To create table student for the schema given below :

Student (no, name, address, class, dob)

The SQL command will be,

CREATE TABLE student (rno number (4), 
sname varchar2 (20), 
address varchar2 (30), 
class char(10), 
dob date);

Viewing a Table Structure

To view an existing table structure, DESCRIBE or simply DESC command is useful. Thus it helps to know,

  • Column(s) name defined in the table.
  • The data type of column.
  • Size of column and
  • Display only NOT NULL constraints column is defined.

Syntax : DESC table_name

E.g. SQL>DESC student;

Note :

Depending upon the option set using SQL *PLUS commands, some column heading might be truncated while using the SELECT command, When you refer to a column in any SQL statement, you need to specify the exact column name and can be viewed through the DESC command.


Altering Table

Before entering data into the column of the table ensure that all the columns have been included because adding or editing fields after entering data into the table will makes users enter data for the new fields using the UPDATE command separately. The ALTER TABLE command is used to modify the structure of the table. Using alter command, the user can perform the following operations,

  • Add new column(s).
  • Add integrity constraints to the existing column.
  • Modify an existing column by increasing the size of the data type.
  • If the table is empty then modifying an existing column by decreasing the size of the data type or changing the data type.
  • Dropping unique or primary key constraints to drop the associated index if any.
  • One can also drop the column, one at a time. (Only allowed in Oracle 9i and higher version)

However, using ALTER TABLE command,

  • Users can not change a column containing NULL to NOT NULL.
  • The user can not rename the column.
  • Users can decrease the column size when the table contains no rows.
  • Users can not drop a column if the table contains only one column.
  • Whenever a new column is added it will become the last column and one can not insert a column in between two columns.

Syntax to add a new column :

ALTER TABLE table_name ADD (COLUMN1 DATATYPE [(size)],.....);

ALTER TABLE

Syntax to modify column :

ALTER TABLE table_name ADD (COLUMN1 DATATYPE [(size)],.....);

ALTER TABLE

Eg. Following commands can be executed on table stud

  1. To add new columns sex and phone statement
  2. To add NOT NULL constraints to the column name
  3. To drop or remove the primary key
  4. To increase the size(width) of the column

DROP COLUMN

One can drop the column, one at a time in Oracle 9i version and higher. The clause DROP column is used with ALTER TABLE command to delete an existing column from the table permanently. Once deleted, you can not undo this operation. If the table consists of only one column, one can not drop this column. Similarly one can not drop a primary key column from the table.

Syntax: ALTER TABLE "table_name" DROP COLUMN "column_name";

SET UNUSED

This option can be used to mark one or more columns to be unused so that they can be dropped later. On setting columns as an UNUSED user can not access the data of that particular column nor its information will be displayed during DESCRIBE structure. Thus it behaves like a drop column only. Once it is marked as UNUSED one can not again use it.

Syntax: ALTER TABLE "table_name" SET UNUSED "column_name";

DROP UNUSED

It is useful to drop all columns that are marked as unused columns. Thus it recovers disk space consumed by marked columns.

Syntax: ALTER TABLE "table_name" DROP UNUSED "column_name";


Dropping Table

The DROP TABLE command is used to delete or destroy the table permanently. Following operations are performed when drop table command is executed,

All the rows from the specified table are deleted.

  • All pending transactions are committed (saved).
  • The structure of the table also gets removed.
  • All indexes associated with the table are dropped.
  • Constraints prevent the deletion of a table if there are dependencies.
  • It does not drop the stored procedure on deleting the table, but it marks procedures as invalid.

Syntax :

DROP objecttype objectname.

Note : For example, the command to drop a table named STUDENT is:

DROP TABLE STUDENT;

The DELETE and TRUNCATE statements are different from the DROP statement in that DELETE and TRUNCATE do not remove the table itself. A DELETE command, for example, deletes data from a table while leaving the table itself in the database, but a DROP statement deletes the entire table.


Truncating Table

To remove all the rows quickly Oracle supports one more command called TRUNCATE which is a part of Data Definition Language unlike DELETE which is a part of DML. It is generally useful to eliminate all the test data that was used for testing new applications.

Syntax:

 TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE TEMP3; 

Share:

Leave a Reply