Data Definition Language (DDL)

What is DDL? / Data Definition Language / Role of Data Definition Language / What can a DDL statement do? / Important aspects of DDL statements

Data Definition Language (DDL)

As specified in the name, DDL is used to define database schemas. With DDL statements we can do the following;

• To create databases.

For example we can create a database named University in MySQL as follows;

CREATE DATABASE University;

• To create table structures.

For example, we can create a table named Student in Oracle as follows;

CREATE TABLE student (Regno NUMBER(10), Name VARCHAR(30));

• To change the structure of the tables.

We can alter the structure of student table by adding/dropping columns, by changing the size of the accepted values etc. For example, the following statement adds a new column with Student table in Oracle;

ALTER TABLE student ADD COLUMN Phone Number(10);

• To remove tables.

We can delete the whole table structure Student using the following statement in Oracle;

DROP TABLE student;

• To rename tables.

To rename an existing table, we can use the following query in Oracle;

RENAME student TO student_table;

• To define referential integrities.

We can define referential integrities like primary key constraints, check constraints, specific type etc using DDL statements. For example, the following query creates a table Teacher with primary key constraint.

CREATE TABLE Teacher(Id NUMBER(3) PRIMARY KEY, Name VARCHAR(30));

• To analyze information.

We can analyze a table, index or clusters for handling performance related issues.

• To add comments to the Data Dictionary.

We can add comments to tables, table columns when they are stored in Data Dictionary. For example, the statement will add a comment ‘Name of the teacher’ with the column definition Name of Teacher table.

COMMENT ON COLUMN Teacher.Name IS ‘Name of the teacher’;

When we execute DDL statements, it does the following things;

1. It does the required things as specified through query. [For example, as mentioned above]


2. Update the special table called Data Dictionary. The Data Dictionary gets updated every time you execute one of the DDL statements.